"SELECT c.id AS c__id, c.lft AS c__lft, c.rgt AS c__rgt, c.level AS c__level, c.root_id AS c__root_id FROM comments c LEFT JOIN sf_guard_user s ON c.user_id = s.id LEFT JOIN votes v ON c.id = v.comment_id WHERE (v.type = 2 OR v.type IS NULL)"
Doctrine seems to be discarding my last leftJoin's with condition. Is this a bug or am I doing something wrong? --
> "SELECT c.id AS c__id, c.lft AS c__lft, c.rgt AS c__rgt, c.level AS > c__level, c.root_id AS c__root_id FROM comments c LEFT JOIN sf_guard_user s > ON c.user_id = s.id LEFT JOIN votes v ON c.id = v.comment_id WHERE (v.type > = 2 OR v.type IS NULL)"
> Doctrine seems to be discarding my last leftJoin's with condition. Is this > a bug or am I doing something wrong? > --
On Wed, May 14, 2008 at 3:13 PM, Jonathan Wage <jonw...@gmail.com> wrote: > Try capitalized WITH? Also I would use tokens in your query. ? instead of > directly concatenating it in to the DQL query.
> - Jon
> On Wed, May 14, 2008 at 1:10 PM, Alex <outroa...@gmail.com> wrote:
>> "SELECT c.id AS c__id, c.lft AS c__lft, c.rgt AS c__rgt, c.level AS >> c__level, c.root_id AS c__root_id FROM comments c LEFT JOIN sf_guard_user s >> ON c.user_id = s.id LEFT JOIN votes v ON c.id = v.comment_id WHERE >> (v.type = 2 OR v.type IS NULL)"
>> Doctrine seems to be discarding my last leftJoin's with condition. Is this >> a bug or am I doing something wrong? >> --
It is good practice. You should always use them so that all values are prepared. In this case it doesn't matter as much since the value isn't necessarily user input.
On Wed, May 14, 2008 at 1:15 PM, Alex <outroa...@gmail.com> wrote: > Bingo! That worked. All DQL commands must be uppercased? What's the > advantage of using the tokens?
> On Wed, May 14, 2008 at 3:13 PM, Jonathan Wage <jonw...@gmail.com> wrote:
>> Try capitalized WITH? Also I would use tokens in your query. ? instead of >> directly concatenating it in to the DQL query.
>> - Jon
>> On Wed, May 14, 2008 at 1:10 PM, Alex <outroa...@gmail.com> wrote:
>>> "SELECT c.id AS c__id, c.lft AS c__lft, c.rgt AS c__rgt, c.level AS >>> c__level, c.root_id AS c__root_id FROM comments c LEFT JOIN sf_guard_user s >>> ON c.user_id = s.id LEFT JOIN votes v ON c.id = v.comment_id WHERE >>> (v.type = 2 OR v.type IS NULL)"
>>> Doctrine seems to be discarding my last leftJoin's with condition. Is >>> this a bug or am I doing something wrong? >>> --
One more question. When must ->select() be added to a Doctrine_Query object? I've noticed that if I select from more than one table Doctrine doesn't select columns from the joined tables. If I have ->from('Table t') , it selects t.* . But if I add ->innerJoin('Table2 t2'), it doesn't select t2.*.
On Wed, May 14, 2008 at 3:18 PM, Jonathan Wage <jonw...@gmail.com> wrote: > It is good practice. You should always use them so that all values are > prepared. In this case it doesn't matter as much since the value isn't > necessarily user input.
> - Jon
> On Wed, May 14, 2008 at 1:15 PM, Alex <outroa...@gmail.com> wrote:
>> Bingo! That worked. All DQL commands must be uppercased? What's the >> advantage of using the tokens?
>> On Wed, May 14, 2008 at 3:13 PM, Jonathan Wage <jonw...@gmail.com> wrote:
>>> Try capitalized WITH? Also I would use tokens in your query. ? instead of >>> directly concatenating it in to the DQL query.
>>> - Jon
>>> On Wed, May 14, 2008 at 1:10 PM, Alex <outroa...@gmail.com> wrote:
>>>> "SELECT c.id AS c__id, c.lft AS c__lft, c.rgt AS c__rgt, c.level AS >>>> c__level, c.root_id AS c__root_id FROM comments c LEFT JOIN sf_guard_user s >>>> ON c.user_id = s.id LEFT JOIN votes v ON c.id = v.comment_id WHERE >>>> (v.type = 2 OR v.type IS NULL)"
>>>> Doctrine seems to be discarding my last leftJoin's with condition. Is >>>> this a bug or am I doing something wrong? >>>> --
On Wed, May 14, 2008 at 1:24 PM, Alex <outroa...@gmail.com> wrote: > One more question. When must ->select() be added to a Doctrine_Query > object? I've noticed that if I select from more than one table Doctrine > doesn't select columns from the joined tables. If I have ->from('Table t') , > it selects t.* . But if I add ->innerJoin('Table2 t2'), it doesn't select > t2.*.
> Couldn't find anything about it in the manual.
> Thanks for your help Jon!
> - alex
> On Wed, May 14, 2008 at 3:18 PM, Jonathan Wage <jonw...@gmail.com> wrote:
>> It is good practice. You should always use them so that all values are >> prepared. In this case it doesn't matter as much since the value isn't >> necessarily user input.
>> - Jon
>> On Wed, May 14, 2008 at 1:15 PM, Alex <outroa...@gmail.com> wrote:
>>> Bingo! That worked. All DQL commands must be uppercased? What's the >>> advantage of using the tokens?
>>> On Wed, May 14, 2008 at 3:13 PM, Jonathan Wage <jonw...@gmail.com> >>> wrote:
>>>> Try capitalized WITH? Also I would use tokens in your query. ? instead >>>> of directly concatenating it in to the DQL query.
>>>> - Jon
>>>> On Wed, May 14, 2008 at 1:10 PM, Alex <outroa...@gmail.com> wrote:
>>>>> "SELECT c.id AS c__id, c.lft AS c__lft, c.rgt AS c__rgt, c.level AS >>>>> c__level, c.root_id AS c__root_id FROM comments c LEFT JOIN sf_guard_user s >>>>> ON c.user_id = s.id LEFT JOIN votes v ON c.id = v.comment_id WHERE >>>>> (v.type = 2 OR v.type IS NULL)"
>>>>> Doctrine seems to be discarding my last leftJoin's with condition. Is >>>>> this a bug or am I doing something wrong? >>>>> --
$query = Doctrine_Query::create()->select('c.*, u.*, vc.*')->from('CommentItem c')->leftJoin('c.sfGuardUser u'); if ($this->getUser()->isAuthenticated()) { $query->leftJoin('c.VoteComment vc WITH vc.user_id = '.$this->getUser()->getId()); }
Gives me
SELECT c.id AS c__id, c.user_id AS c__user_id, c.body AS c__body, c.votes_for_cache AS c__votes_for_cache, c.votes_against_cache AS c__votes_against_cache, c.created_at AS c__created_at, c.updated_at AS c__updated_at, c.root_id AS c__root_id, c.lft AS c__lft, c.rgt AS c__rgt, c.level AS c__level, s.id AS s__id, s.username AS s__username, s.algorithm AS s__algorithm, s.salt AS s__salt, s.password AS s__password, s.last_login AS s__last_login, s.is_active AS s__is_active, s.is_super_admin AS s__is_super_admin, s.created_at AS s__created_at, s.updated_at AS s__updated_at, v.id AS v__id, v.user_id AS v__user_id, v.vote_type_id AS v__vote_type_id, v.type AS v__type, v.item_id AS v__item_id, v.comment_id AS v__comment_id, v.created_at AS v__created_at FROM comments c LEFT JOIN sf_guard_user s ON c.user_id = s.id LEFT JOIN votes v ON c.id = v.comment_id AND v.user_id = 1 WHERE (v.type = 2 OR v.type IS NULL)
$query = Doctrine_Query::create()->from('CommentItem c')->leftJoin('c.sfGuardUser u'); if ($this->getUser()->isAuthenticated()) { $query->leftJoin('c.VoteComment vc WITH vc.user_id = '.$this->getUser()->getId()); }
Gives me
SELECT c.id AS c__id, c.lft AS c__lft, c.rgt AS c__rgt, c.level AS c__level, c.root_id AS c__root_id FROM comments c LEFT JOIN sf_guard_user s ON c.user_id = s.id LEFT JOIN votes v ON c.id = v.comment_id AND v.user_id = 1 WHERE (v.type = 2 OR v.type IS NULL)
I'm using $treeObject->setBaseQuery($query); echo $treeObject->getBaseQuery()->getSqlQuery();
Could the treeObject be altering default Doctrine behavior?
On Wed, May 14, 2008 at 4:09 PM, Jonathan Wage <jonw...@gmail.com> wrote: > Hmm. Are you sure about that? If you don't specify a select() in 1.0 it > will default to select all columns across all tables.
> - Jon
> On Wed, May 14, 2008 at 1:24 PM, Alex <outroa...@gmail.com> wrote:
>> One more question. When must ->select() be added to a Doctrine_Query >> object? I've noticed that if I select from more than one table Doctrine >> doesn't select columns from the joined tables. If I have ->from('Table t') , >> it selects t.* . But if I add ->innerJoin('Table2 t2'), it doesn't select >> t2.*.
>> Couldn't find anything about it in the manual.
>> Thanks for your help Jon!
>> - alex
>> On Wed, May 14, 2008 at 3:18 PM, Jonathan Wage <jonw...@gmail.com> wrote:
>>> It is good practice. You should always use them so that all values are >>> prepared. In this case it doesn't matter as much since the value isn't >>> necessarily user input.
>>> - Jon
>>> On Wed, May 14, 2008 at 1:15 PM, Alex <outroa...@gmail.com> wrote:
>>>> Bingo! That worked. All DQL commands must be uppercased? What's the >>>> advantage of using the tokens?
>>>> On Wed, May 14, 2008 at 3:13 PM, Jonathan Wage <jonw...@gmail.com> >>>> wrote:
>>>>> Try capitalized WITH? Also I would use tokens in your query. ? instead >>>>> of directly concatenating it in to the DQL query.
>>>>> - Jon
>>>>> On Wed, May 14, 2008 at 1:10 PM, Alex <outroa...@gmail.com> wrote:
>>>>>> "SELECT c.id AS c__id, c.lft AS c__lft, c.rgt AS c__rgt, c.level AS >>>>>> c__level, c.root_id AS c__root_id FROM comments c LEFT JOIN sf_guard_user s >>>>>> ON c.user_id = s.id LEFT JOIN votes v ON c.id = v.comment_id WHERE >>>>>> (v.type = 2 OR v.type IS NULL)"
>>>>>> Doctrine seems to be discarding my last leftJoin's with condition. Is >>>>>> this a bug or am I doing something wrong? >>>>>> --
On Wed, May 14, 2008 at 2:14 PM, Alex <outroa...@gmail.com> wrote: > $query = Doctrine_Query::create()->select('c.*, u.*, > vc.*')->from('CommentItem c')->leftJoin('c.sfGuardUser u'); > if ($this->getUser()->isAuthenticated()) > { > $query->leftJoin('c.VoteComment vc WITH vc.user_id = > '.$this->getUser()->getId()); > }
> Gives me
> SELECT c.id AS c__id, c.user_id AS c__user_id, c.body AS c__body, > c.votes_for_cache AS c__votes_for_cache, c.votes_against_cache AS > c__votes_against_cache, c.created_at AS c__created_at, c.updated_at AS > c__updated_at, c.root_id AS c__root_id, c.lft AS c__lft, c.rgt AS c__rgt, > c.level AS c__level, s.id AS s__id, s.username AS s__username, s.algorithm > AS s__algorithm, s.salt AS s__salt, s.password AS s__password, s.last_login > AS s__last_login, s.is_active AS s__is_active, s.is_super_admin AS > s__is_super_admin, s.created_at AS s__created_at, s.updated_at AS > s__updated_at, v.id AS v__id, v.user_id AS v__user_id, v.vote_type_id AS > v__vote_type_id, v.type AS v__type, v.item_id AS v__item_id, v.comment_id AS > v__comment_id, v.created_at AS v__created_at FROM comments c LEFT JOIN > sf_guard_user s ON c.user_id = s.id LEFT JOIN votes v ON c.id = > v.comment_id AND v.user_id = 1 WHERE (v.type = 2 OR v.type IS NULL)
> SELECT c.id AS c__id, c.lft AS c__lft, c.rgt AS c__rgt, c.level AS > c__level, c.root_id AS c__root_id FROM comments c LEFT JOIN sf_guard_user s > ON c.user_id = s.id LEFT JOIN votes v ON c.id = v.comment_id AND v.user_id > = 1 WHERE (v.type = 2 OR v.type IS NULL)
> I'm using $treeObject->setBaseQuery($query); > echo $treeObject->getBaseQuery()->getSqlQuery();
> Could the treeObject be altering default Doctrine behavior?
> On Wed, May 14, 2008 at 4:09 PM, Jonathan Wage <jonw...@gmail.com> wrote:
>> Hmm. Are you sure about that? If you don't specify a select() in 1.0 it >> will default to select all columns across all tables.
>> - Jon
>> On Wed, May 14, 2008 at 1:24 PM, Alex <outroa...@gmail.com> wrote:
>>> One more question. When must ->select() be added to a Doctrine_Query >>> object? I've noticed that if I select from more than one table Doctrine >>> doesn't select columns from the joined tables. If I have ->from('Table t') , >>> it selects t.* . But if I add ->innerJoin('Table2 t2'), it doesn't select >>> t2.*.
>>> Couldn't find anything about it in the manual.
>>> Thanks for your help Jon!
>>> - alex
>>> On Wed, May 14, 2008 at 3:18 PM, Jonathan Wage <jonw...@gmail.com> >>> wrote:
>>>> It is good practice. You should always use them so that all values are >>>> prepared. In this case it doesn't matter as much since the value isn't >>>> necessarily user input.
>>>> - Jon
>>>> On Wed, May 14, 2008 at 1:15 PM, Alex <outroa...@gmail.com> wrote:
>>>>> Bingo! That worked. All DQL commands must be uppercased? What's the >>>>> advantage of using the tokens?
>>>>> On Wed, May 14, 2008 at 3:13 PM, Jonathan Wage <jonw...@gmail.com> >>>>> wrote:
>>>>>> Try capitalized WITH? Also I would use tokens in your query. ? instead >>>>>> of directly concatenating it in to the DQL query.
>>>>>> - Jon
>>>>>> On Wed, May 14, 2008 at 1:10 PM, Alex <outroa...@gmail.com> wrote:
>>>>>>> "SELECT c.id AS c__id, c.lft AS c__lft, c.rgt AS c__rgt, c.level AS >>>>>>> c__level, c.root_id AS c__root_id FROM comments c LEFT JOIN sf_guard_user s >>>>>>> ON c.user_id = s.id LEFT JOIN votes v ON c.id = v.comment_id WHERE >>>>>>> (v.type = 2 OR v.type IS NULL)"
>>>>>>> Doctrine seems to be discarding my last leftJoin's with condition. Is >>>>>>> this a bug or am I doing something wrong? >>>>>>> --
On May 14, 9:14 pm, Alex <outroa...@gmail.com> wrote:
> Could the treeObject be altering default Doctrine behavior?
Yes it does. The tree object auto-adds lft/rgt/level to the select
part, hence even if you dont use a select() in your query it ends up
as select lft/rgt/level, then later it reaches the dql parser and
there the select part is not missing, so no fields (except PKs) are
auto-added.
Thats the explanation for the behavior. What is questionable here is
that the tree object may not need to add these select bits IF the
custom query has no select part at all since the parser will then auto-
select all of them. For this issue you can open a ticket.
