Joining a table with itself

Showing 1-12 of 12 messages
Joining a table with itself Rytis Daugirdas 1/27/11 3:15 AM
Hello,

I'm having a bit of a problem using ModelCriteria to construct a query
that joins a table with itself. Here's a simple example:

Model: Choice
Table: Choices
Columns: user: int, choice: int

I'd like to generate the following query and retrieve results as a
collection of associative arrays:

select count(t1.user) as users, t1.choice as lft, t2.choice as rgt
from Choices t1 inner join Choices t2 on (t1.user = t2.user) where
t1.choice in (...) and t2.choice in (...) group by t1.choice,
t2.choice;

Is this possible with the propel-generated ChoiceQuery? It seems that
I have to combine the "new" ModelCriteria methods with the "old"
Criteria/Peer methods and when doing so things seem to break and the
resulting SQL query is invalid.
Re: Joining a table with itself Rytis Daugirdas 1/27/11 5:45 AM
I got it working, but by using Criteria-only methods.

However, I'd still appreciate an example of idiomatic ModelCriteria
usage in this case (if at all possible). Right now the code contains
references to Peer methods and constants which makes the code look
more bloated than it could be.
Re: [propel] Joining a table with itself Francois Zaninotto 1/31/11 12:29 AM
Hi Rytis,

Using Propel 1.5 and ModelCriteria, it's trivial. See examples in the documentation:


Cheers,

François


--
You received this message because you are subscribed to the Google Groups "Propel Users" group.
To post to this group, send email to propel...@googlegroups.com.
To unsubscribe from this group, send email to propel-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/propel-users?hl=en.


Re: Joining a table with itself Rytis Daugirdas 1/31/11 12:38 AM
I think you're too quick to point that example since it does not
correspond to my situation. Would you mind providing a code snippet to
generate the SQL query via ModelCriteria-only methods? The query is
not complicated at all.

On Jan 31, 10:29 am, Francois Zaninotto <fzanino...@gmail.com> wrote:
> Hi Rytis,
>
> Using Propel 1.5 and ModelCriteria, it's trivial. See examples in the
> documentation:
>
> http://www.propelorm.org/wiki/Documentation/1.5/ModelCriteria#TableAl...
>
> <http://www.propelorm.org/wiki/Documentation/1.5/ModelCriteria#TableAl...>
> Cheers,
>
> François
>
> Le 27 janvier 2011 12:15:06 UTC+1, Rytis Daugirdas <
> rytis.daugir...@gmail.com> a écrit :
>
> > Hello,
>
> > I'm having a bit of a problem using ModelCriteria to construct a query
> > that joins a table with itself. Here's a simple example:
>
> > Model: Choice
> > Table: Choices
> > Columns: user: int, choice: int
>
> > I'd like to generate the following query and retrieve results as a
> > collection of associative arrays:
>
> > select count(t1.user) as users, t1.choice as lft, t2.choice as rgt
> > from Choices t1 inner join Choices t2 on (t1.user = t2.user) where
> > t1.choice in (...) and t2.choice in (...) group by t1.choice,
> > t2.choice;
>
> > Is this possible with the propel-generated ChoiceQuery? It seems that
> > I have to combine the "new" ModelCriteria methods with the "old"
> > Criteria/Peer methods and when doing so things seem to break and the
> > resulting SQL query is invalid.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "Propel Users" group.
> > To post to this group, send email to propel...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > propel-users...@googlegroups.com<propel-users%2Bunsubscribe@googlegroups.com>
> > .
Re: [propel] Re: Joining a table with itself Francois Zaninotto 1/31/11 1:02 AM
I think you're too quick to dismiss this example since it contains a snippet of a join to the same table.

You have to define phpName and refPhpName for the self-referencing fKey in your schema.

François

2011/1/31 Rytis Daugirdas <rytis.d...@gmail.com>
To unsubscribe from this group, send email to propel-users...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/propel-users?hl=en.


Re: Joining a table with itself Rytis Daugirdas 2/1/11 12:18 AM
There are no self-referencing fkeys in my example. Neither column
"user" nor "choice" refer to any pkeys in the Choices table. They
refer to other tables. Besides, why would I want to explicitly define
phpName when it's always autogenerated for me (my_column => MyColumn)?

On Jan 31, 11:02 am, Francois Zaninotto <fzanino...@gmail.com> wrote:
> I think you're too quick to dismiss this example since it contains a snippet
> of a join to the same table.
>
> You have to define phpName and refPhpName for the self-referencing fKey in
> your schema.
>
> François
>
> 2011/1/31 Rytis Daugirdas <rytis.daugir...@gmail.com>
> > <propel-users%...@googlegroups.com<propel-users%252Bunsubscribe@googlegroups.com>
Re: [propel] Re: Joining a table with itself Francois Zaninotto 2/1/11 12:33 AM
Then I think your model is incomplete. If you reason with objects rather than tables, you can see that your 'Choice' model has a relationship with itself - you use it in your query.

In an ORM, relationships are supported by foreign keys. Whether you like it or not, fkeys enforce constraints and create indices, so that's the good way.

Giving a name to both ends of a relationship is essential for better comprehension. For instance, if a User references another User in a subordination relationship, one end of the relationship sees a 'Supervisor', another end sees a 'Sobordinate'. If both ends only see a 'User', it doesn't make sense.

François

2011/2/1 Rytis Daugirdas <rytis.d...@gmail.com>
To unsubscribe from this group, send email to propel-users...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/propel-users?hl=en.


Re: Joining a table with itself Rytis Daugirdas 2/1/11 1:05 AM
The model is fine. The query in question is not designed to work with
objects at all. It selects only certain columns (some of which are
calculated while others are not). I don't need nor want objects
returned (it's logically impossible even), I think I stated this
clearly in the example's description.

Nobody works with objects all the time since sometimes moving down to
the table/row/column abstraction is more useful. I wouldn't be asking
this question if Propel was ONLY suitable for working with objects
(which is clearly not the case).

On Feb 1, 10:33 am, Francois Zaninotto <fzanino...@gmail.com> wrote:
> Then I think your model is incomplete. If you reason with objects rather
> than tables, you can see that your 'Choice' model has a relationship with
> itself - you use it in your query.
>
> In an ORM, relationships are supported by foreign keys. Whether you like it
> or not, fkeys enforce constraints and create indices, so that's the good
> way.
>
> Giving a name to both ends of a relationship is essential for better
> comprehension. For instance, if a User references another User in a
> subordination relationship, one end of the relationship sees a 'Supervisor',
> another end sees a 'Sobordinate'. If both ends only see a 'User', it doesn't
> make sense.
>
> François
>
> 2011/2/1 Rytis Daugirdas <rytis.daugir...@gmail.com>
> > > > <propel-users%...@googlegroups.com<propel-users%252Bunsubscribe@googlegroups.com>
> > <propel-users%2...@googlegroups.com<propel-users%25252Bunsubscribe@googlegroups.com>
Re: [propel] Re: Joining a table with itself Francois Zaninotto 2/1/11 4:58 AM
Then ModelCriteria can't help you in this case. It works with relations. If there is no relation, you'll have to use the lower-level Criteria/Peer API.

François

2011/2/1 Rytis Daugirdas <rytis.d...@gmail.com>
The model is fine. The query in question is not designed to work with
To unsubscribe from this group, send email to propel-users...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/propel-users?hl=en.


Re: Joining a table with itself xplo 2/1/11 4:59 AM
Hi,

you cannot do arbitrary join with modelCriteria API since this API is
linked to your model.
Francois is right, if you need a join even if it s self joined you
need to describe it in your schema.

The select method is the closest of what you need to use but you said
it yourself you need to use raw sql since it s not compatible with the
object representation.
> > > <propel-users%2...@googlegroups.com<propel-users%25252Bunsubscri b...@googlegroups.com>
Re: Joining a table with itself Rytis Daugirdas 2/1/11 5:43 AM
OK, thanks for confirming that.

On Feb 1, 2:58 pm, Francois Zaninotto <fzanino...@gmail.com> wrote:
> Then ModelCriteria can't help you in this case. It works with relations. If
> there is no relation, you'll have to use the lower-level Criteria/Peer API.
>
> François
>
> 2011/2/1 Rytis Daugirdas <rytis.daugir...@gmail.com>
> > > > > > <propel-users%...@googlegroups.com<propel-users%252Bunsubscribe@googlegroups.com>
> > <propel-users%2...@googlegroups.com<propel-users%25252Bunsubscribe@googlegroups.com>
>
> > > > <propel-users%2...@googlegroups.com<propel-users%25252Bunsubscribe@googlegroups.com>
> > <propel-users%25252Bunsubscribe@googlegroups.com<propel-users%2525252Bunsubscribe@googlegroups.com>
Re: Joining a table with itself Rytis Daugirdas 2/1/11 5:47 AM
I don't need to use raw SQL. Criteria/Peer API works fine for what I
needed. I just wanted to find out whether the same can be done using
ModelCriteria API, which, as Francois said, is not possible.