[Solar-talk] RE: Interesting SQL Error from Model

[Kilbride, James P.]

So I figured out the problem. It's in the name of the relationship I'm
using(which is called currently procedure, since it's pulling back the
parent procedure). Apparently you can't use reserved words even in the
names of the foreign relationships because the model code uses it as the
as statement in the query which is why procedure ends up in the select
query even though I didn't name the table procedure. I'm not sure what I
think about this since solar should either capture that the relationship
is a 'reserved word' and complain rather than going to the sql before it
does, or it shouldn't be using the relationship name in the sql query.
I'm actually more on the lines that it shouldn't be using the
relationship name in the query myself but perhaps there is a reason for
it.

James Kilbride

> _____________________________________________ 
> From: 	Kilbride, James P.  
> Sent:	Friday, May 02, 2008 9:23 AM
> To:	'solar-talk at lists.solarphp.com'
> Subject:	Interesting SQL Error from Model
> 
> I've got a model which seems to be giving me this error from my
> database:
> 
> 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an
> error in your SQL syntax; check the manual that corresponds to your
> MySQL server version for the right syntax to use near \'procedure
> WHERE procedure.procedure_id = 101 ORDER BY procedure.procedure_id A\'
> at line 17'
> 
> The class name is procedure, the table name is documented_procedure,
> though it looks like the database has it called procedure, not sure
> why on that one. Here's the full stmt according to the PDO output:
> 
> 'SELECT procedure_id, document_id, procedure_number, procedure_title,
> approver, publisher, distributor, requested_support, nsi, uk, type,
> documentation_type, other_developers, remarks, active FROM
> documented_procedure AS procedure WHERE procedure.procedure_id = 101
> ORDER BY procedure.procedure_id ASC LIMIT 1'
> This is occurring because I"ve got a related table(matrix) which has a
> procedure_id in it and is a child of the procedure table. I'm trying
> to get the documented_procedure row for that matrix. 
> 
> would the class code help at all? If so it's below.
> 
> James Kilbride
> 
> class ProcedureMatrix_Models_VerificationMatrix  extends
> Solar_Sql_Model
> {
>     protected function _setup()
>     {
...    	
>     	$this->_belongsTo('procedure', array (
>     		'foreign_class' => 'ProcedureMatrix_Models_Procedure',
>     		'foreign_key' 	=> 'procedure_id',
>     	));
>     }
...
> }
> 
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman-mail3.webfaction.com/pipermail/solar-talk/attachments/20080502/3315f779/attachment.html