LEFT JOIN / joined results not handled - doctrine-user

Message from discussion LEFT JOIN / joined results not handled
Received-SPF: pass (google.com: domain of jonw...@gmail.com designates 209.85.219.225 as permitted sender) client-ip=209.85.219.225;
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:from:date:message-id:subject:to
         :content-type;
        b=Ec3sexPHw3UmBmHntQNma/wZE2Q+mIXUeHE5Y/DfBWNXEFSIyzKOvru+wsqHt9qshr
         qSqrjAVu+7ZwzrY1joyAMKPF9QWW1nwbnKepTvvIKjTcz6vfOaUzeQX/oOhfIKYSpWQy
         xJVnzyHQkhSHoG2qOyQXjPKGKiJXSb/LfH6iM=
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="0016367b6af2b5e760046e35ca42"
In-Reply-To: <9c6800b1-3f7a-417a-89a8-b44c0e6e745d@q11g2000yqi.googlegroups.com>
References: <9c6800b1-3f7a-417a-89a8-b44c0e6e745d@q11g2000yqi.googlegroups.com>
From: Jonathan Wage <jonw...@gmail.com>
Date: Wed, 8 Jul 2009 13:20:00 -0500
Message-ID: <be85b5220907081120t3ed4d652i21a33bc6edbbc...@mail.gmail.com>
Subject: Re: [doctrine-user] LEFT JOIN / joined results not handled
To: doctrine-user@googlegroups.com

--0016367b6af2b5e760046e35ca42
Content-Type: text/plain; charset=ISO-8859-1

I think it is because you are not selecting enough data from the cc alias.
Try just changing it to a wide open select by removing the select() line.

- Jon

On Wed, Jul 8, 2009 at 1:18 PM, James Tucker <tenlet...@gmail.com> wrote:

>
> Hello there,
>
> i encountered a problem, when using a left join command in a query.
> Basically, what happened is that my joined results were not appearing
> in the final results of the query. After carefully testing the
> validity of the actual query (by testing the query to mysql directly)
> i realized that the problem can not originate from mysql as such.
>
> A few details:
>
> Model A:
> Consists of an (primary key) 'id' of type string, length 16
> and a few other fields, most importantly for this example a field
> 'code' (string/10)
>
> Model B:
> Has a field 'mandantPath' that is related to model A (field id)
> through a hasOne() relation.
>
> #code
> $this->hasOne('mandant_db_node as mandant', array
> ('local'=>'mandantPath','foreign'=>'id'));
>
> #here is my query
>
>        $l=Doctrine_Query::create()
>                         ->select('*,p.code')
>                         ->from('contact_db_customer cc')
>                         ->leftJoin('cc.mandant p')
>                         ->where($w)
>                         ->offset($request['startindex']+0)
>                         ->limit($request['rows']+0)
>                         ->orderBy($order)
>                         ->execute(array());
>
> Now, if i call this the way i do (above), i get the basic results
> (fields), but not the joined ones. If i get the SQL query generated by
> Doctrine, and use it directly, it works just fine and it returns the
> 'code' field as it should.
>
> However, when i add type Doctrine::HYDRATE_ARRAY to the execute
> command, i get the correct results, but of course encapsuled.. so that
> i have to loop/parse through the result and set the values according
> to my demands.
>
> Pretty frustrating - while i am certain that the problem is very basic
> and i just lack the knowledge of a proper solution. Hopefully someone
> in here knows what i am dealing with - specifically, what it is that i
> am doing wrong.
>
> Kind regards, James
> >
>


-- 
Jonathan H. Wage (+1 415 992 5468)
Open Source Software Developer & Evangelist
sensiolabs.com | jwage.com | doctrine-project.org | symfony-project.org

You can contact Jonathan about Doctrine, Symfony and Open-Source or for
training, consulting, application development, or business related questions
at jonathan.w...@sensio.com

--0016367b6af2b5e760046e35ca42
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I think it is because you are not selecting enough data from the cc alias. =
Try just changing it to a wide open select by removing the select() line.<b=
r><br>- Jon<br><br><div class=3D"gmail_quote">On Wed, Jul 8, 2009 at 1:18 P=
M, James Tucker <span dir=3D"ltr">&lt;<a href=3D"mailto:tenlet...@gmail.com=
">tenlet...@gmail.com</a>&gt;</span> wrote:<br>

<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><br>
Hello there,<br>
<br>
i encountered a problem, when using a left join command in a query.<br>
Basically, what happened is that my joined results were not appearing<br>
in the final results of the query. After carefully testing the<br>
validity of the actual query (by testing the query to mysql directly)<br>
i realized that the problem can not originate from mysql as such.<br>
<br>
A few details:<br>
<br>
Model A:<br>
Consists of an (primary key) &#39;id&#39; of type string, length 16<br>
and a few other fields, most importantly for this example a field<br>
&#39;code&#39; (string/10)<br>
<br>
Model B:<br>
Has a field &#39;mandantPath&#39; that is related to model A (field id)<br>
through a hasOne() relation.<br>
<br>
#code<br>
$this-&gt;hasOne(&#39;mandant_db_node as mandant&#39;, array<br>
(&#39;local&#39;=3D&gt;&#39;mandantPath&#39;,&#39;foreign&#39;=3D&gt;&#39;i=
d&#39;));<br>
<br>
#here is my query<br>
<br>
 =A0 =A0 =A0 =A0$l=3DDoctrine_Query::create()<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -&gt;select(&#39;*,p.code&=
#39;)<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -&gt;from(&#39;contact_db_=
customer cc&#39;)<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -&gt;leftJoin(&#39;cc.mand=
ant p&#39;)<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -&gt;where($w)<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -&gt;offset($request[&#39;=
startindex&#39;]+0)<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -&gt;limit($request[&#39;r=
ows&#39;]+0)<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -&gt;orderBy($order)<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -&gt;execute(array());<br>
<br>
Now, if i call this the way i do (above), i get the basic results<br>
(fields), but not the joined ones. If i get the SQL query generated by<br>
Doctrine, and use it directly, it works just fine and it returns the<br>
&#39;code&#39; field as it should.<br>
<br>
However, when i add type Doctrine::HYDRATE_ARRAY to the execute<br>
command, i get the correct results, but of course encapsuled.. so that<br>
i have to loop/parse through the result and set the values according<br>
to my demands.<br>
<br>
Pretty frustrating - while i am certain that the problem is very basic<br>
and i just lack the knowledge of a proper solution. Hopefully someone<br>
in here knows what i am dealing with - specifically, what it is that i<br>
am doing wrong.<br>
<br>
Kind regards, James<br>
<br>
</blockquote></div><br><br clear=3D"all"><br>-- <br>Jonathan H. Wage (+1 41=
5 992 5468)<br>Open Source Software Developer &amp; Evangelist<br><a href=
=3D"http://sensiolabs.com">sensiolabs.com</a> | <a href=3D"http://jwage.com=
">jwage.com</a> | <a href=3D"http://doctrine-project.org">doctrine-project.=
org</a> | <a href=3D"http://symfony-project.org">symfony-project.org</a><br=
>

<br>You can contact Jonathan about Doctrine, Symfony and Open-Source or for=
 training, consulting, application development, or business related questio=
ns at <a href=3D"mailto:jonathan.w...@sensio.com">jonathan.w...@sensio.com<=
/a><br>



--0016367b6af2b5e760046e35ca42--