LEFT JOIN / joined results not handled - doctrine-user

Message from discussion LEFT JOIN / joined results not handled
MIME-Version: 1.0
Date: Thu, 9 Jul 2009 02:19:13 -0700 (PDT)
In-Reply-To: <be85b5220907081120t3ed4d652i21a33bc6edbbca14@mail.gmail.com>
References: <9c6800b1-3f7a-417a-89a8-b44c0e6e745d@q11g2000yqi.googlegroups.com> 
	<be85b5220907081120t3ed4d652i21a33bc6edbbca14@mail.gmail.com>
User-Agent: G2/1.0
Message-ID: <d7cefc2f-816e-4d43-b5ec-42d982b749d5@37g2000yqp.googlegroups.com>
Subject: Re: LEFT JOIN / joined results not handled
From: James Tucker <tenlet...@gmail.com>
To: doctrine-user <doctrine-user@googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Thank you for the hint,

i tried it, without select, but the results were exactly the same.

Does anyone know what is going wrong here?

On 8 Jul., 20:20, Jonathan Wage <jonw...@gmail.com> wrote:
> 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'=3D>'mandantPath','foreign'=3D>'id'));
>
> > #here is my query
>
> > =A0 =A0 =A0 =A0$l=3DDoctrine_Query::create()
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ->select('*,p.code')
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ->from('contact_db_cust=
omer cc')
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ->leftJoin('cc.mandant =
p')
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ->where($w)
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ->offset($request['star=
tindex']+0)
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ->limit($request['rows'=
]+0)
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ->orderBy($order)
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ->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 questi=
ons