Problem with PDO and left join

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem with PDO and left join

Paladyn
This post has NOT been accepted by the mailing list yet.
I`ve got problem with query with two arguments in ON clause. Query works perfect in pure SQL but here it gives me two exceptions:
"Statement could not be executed"
"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 '`and` `c`.`type` = `news`' at line 1"

$sql = new Sql($this->adapter);
$select = $sql->select();
$select->from(array('t' => 'table'));
$select->join(array('c' => 'content'),
            't.id = c.table_id and c.type = news',
    array('content','active'),
         'left');
luk
Reply | Threaded
Open this post in threaded view
|

Re: Problem with PDO and left join

luk
Paladyn wrote
I`ve got problem with query with two arguments in ON clause. Query works perfect in pure SQL but here it gives me two exceptions:
"Statement could not be executed"
"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 '`and` `c`.`type` = `news`' at line 1"

$sql = new Sql($this->adapter);
$select = $sql->select();
$select->from(array('t' => 'table'));
$select->join(array('c' => 'content'),
            't.id = c.table_id and c.type = news',
    array('content','active'),
         'left');
I'm not sure 100% but by the first look you would need to move your bit "and c.type=news" into where condition or try to put quotes around "news".
Cheers, -- Luke Mierzwa
Reply | Threaded
Open this post in threaded view
|

Re: Problem with PDO and left join

Paladyn
This post has NOT been accepted by the mailing list yet.
I`ve tried many differend versions with quotes alredy but that won`t help.
Where condition changes query a bit, so it`s not an option I think.
luk
Reply | Threaded
Open this post in threaded view
|

Re: Problem with PDO and left join

luk
Paladyn wrote
I`ve tried many differend versions with quotes alredy but that won`t help.
Where condition changes query a bit, so it`s not an option I think.
You may try to pass that as an array:
$select->join(array('c' => 'content'),
            array('t.id = c.table_id', 'c.type = news'),
    array('content','active'),

Otherwise please try to debug the produced SQL query and check what it's outputting.
Cheers, -- Luke Mierzwa
Reply | Threaded
Open this post in threaded view
|

Re: Problem with PDO and left join

Paladyn
This post has NOT been accepted by the mailing list yet.
I`ve tried this also. Second argument of join function can only be string ("Warning: preg_split() expects parameter 2 to be string, array given in C:\xampp\htdocs\zend2test\vendor\zendframework\zendframework\library\Zend\Db\Adapter\Platform\Mysql.php on line 122").

I`ve checked select array and everything looks alright ([joins:protected] => Array ( [0] => Array ( [name] => Array ( [c] => content ) [on] => t.id = c.table_id and c.type = news [columns] => Array ( [0] => content [1] => active ) [type] => left ) )

Using my query in phpMyAdmin gives me result (without errors) I want to have.
Reply | Threaded
Open this post in threaded view
|

Re: Problem with PDO and left join

Paladyn
This post has NOT been accepted by the mailing list yet.
I`m a bit closer to answer right now. Changing and to AND makes query more suitable. Now I have problem with: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'news' in 'on clause'.
I`m trying to add different quotes around "news" but it`s not helping.

Reply | Threaded
Open this post in threaded view
|

Re: Problem with PDO and left join

Paladyn
This post has NOT been accepted by the mailing list yet.
After trying few ways, I can`t get through it. I started to think that it can be a bug.
Reply | Threaded
Open this post in threaded view
|

Re: Problem with PDO and left join

HHGAG
This post was updated on .
I had reported that issue: http://framework.zend.com/issues/browse/ZF2-504
Hasan H. Gürsoy (HHGAG)
Reply | Threaded
Open this post in threaded view
|

Re: Problem with PDO and left join

Paladyn
This post has NOT been accepted by the mailing list yet.
Maybe add link to this thread in bug report. It can help them to resolve problem.
Reply | Threaded
Open this post in threaded view
|

Re: Problem with PDO and left join

HHGAG
This post has NOT been accepted by the mailing list yet.
I've reported it again: https://github.com/zendframework/zf2/issues/2691
Hasan H. Gürsoy (HHGAG)
Reply | Threaded
Open this post in threaded view
|

Re: Problem with PDO and left join

Paladyn
This post has NOT been accepted by the mailing list yet.
This post was updated on .
I`ve tryed ralphschindler idea and it helped (with small modification).

$sql = new Sql($this->adapter);
$select = $sql->select();
$select->from(array('t' => 'table'));
$select->join(array('c' => 'content'),
            new Expression('t.id = c.table_id AND c.type = "news"'),
    array('content','active'),
         'left');

Name of the column must be in ". Without it ZF2 returns SQLSTATE[42S22]: Column not found: 1054 Unknown column 'news' in 'on clause'.

I hope it will help someone.