Having trouble with CDBCommand

Archives Yii 1.0.x Discussions
1

Hi, im having some trouble when trying to paginate manually some sql, here is the code:




$offset = 3;

$limit = 3;

$paginated = "SELECT l.ID FROM NODE n

				JOIN (LEAF l, NODE_LEAF nl, EXTRA_LEAF e, LEAF_RESOURCE lr, EXTRA_NODE en)

				ON (l.ID = nl.LEAF_ID AND n.ID = nl.NODE_ID AND e.LEAF_ID = l.ID AND lr.LEAF_ID = l.ID AND en.NODE_ID = n.ID)

				WHERE en.NODE_ID  = {$this->id}

				ORDER BY l.PUBLICATION_START_DATE DESC LIMIT $offset, $limit;";


$conn = Yii::app()->db;

$command = $conn->createCommand($paginated);

$reader	= $command->queryColumn();

echo $paginated;

print_r($reader);

Im echoing the sql, if y copy/paste that into my QueryBrowser i get:

7142

7139

But when i print out the reader i get:

Array ( [0] => 7141 [1] => 7139 )

That just makes no sense and its driving me nuts, why do the results differ if its the same query?

Any ideas?

2

My guess is that it’s due to some difference between PDO and (your) QueryBrowser.

What if you explicitly specify LEFT JOIN/RIGHT JOIN?

/Tommy

3

Mmm you where right, if I specifi LEFT JOIN in the query browser i get the same result as with yii.

The thing is that the correct answer for me is

7142

7139

How can i solve this? still i dont understand why if im passing an SQl as a string it gets parsed and changed from JOIN to LEFT JOIN?

4

Have you tried RIGHT JOIN in Yii?

On a side note: I was probably wrong mentioning PDO, it’s most probably a difference at a lower level.

Edit:

I think it’s clear that this is at least not at all Yii related.

Since I’m not an DB expert, my suggestion - if RIGHT JOIN doesn’t help - is that you rearrange, or probably even better, break apart the JOIN operations for better control. You didn’t mention which DB you use. Here’s a link to the mySQL manual:

http://dev.mysql.com/doc/refman/5.0/en/join.html

/Tommy

5

thanks for your help, im using MySQl:

I dont know why but your suggestion of separating joins worked:

my query now looks like:




$paginated = "SELECT l.ID FROM NODE n

				JOIN NODE_LEAF nl ON n.ID = nl.NODE_ID

				JOIN LEAF l ON l.ID = nl.LEAF_ID

				JOIN EXTRA_LEAF e ON e.LEAF_ID = l.ID

				JOIN LEAF_RESOURCE lr ON lr.LEAF_ID = l.ID

				JOIN EXTRA_NODE en ON en.NODE_ID = n.ID

				WHERE en.NODE_ID IN (SELECT id FROM Node WHERE node_id = {$this->id})

				ORDER BY l.PUBLICATION_START_DATE DESC LIMIT $offset, $limit;"

And now it does return the correct result.

Thanks =}