You are viewing a plain text version of this content. The canonical link for it is here.

Posted to torque-user@db.apache.org by Derek Hardy <de...@teknosys.co.uk> on 2003/05/13 10:32:12 UTC

Question regarding Peers, Joins and Criteria

Hi

I'm trying to create a method in the PropertyPeer to reproduce the following
SQL query

select *
  from property p
      ,property_stores s
 where p.store_id = s.store_id
   and s.owner_id = 1;

How do I define the criteria to add a restriction on the join table.

Thanks

Derek

Re: Question regarding Peers, Joins and Criteria

Posted by Eric Emminger <er...@ericemminger.com>.

Derek

Derek Hardy wrote:
> Thanks for your assistance Eric.
> 
> I wonder if I could trouble you for one further piece of assistance that
> would also answer an earlier query for me.
> 
> Using your code below, I added a new line
> 
> Criteria crit = new Criteria();
> crit.addSelectColumn("count("+PropertyPeer.PROPERTY_ID+")");   // New line
> added by DH
> crit.addJoin(PropertyPeer.STORE_ID, PropertyStoresPeer.STORE_ID);
> crit.add(PropertyStoresPeer.OWNER_ID, 1);
> List results = PropertyPeer.doSelect(crit);
> 
> I get exactly the SQL I am trying to produce
> 
> SELECT count(Property.PROPERTY_ID)
>   FROM Property, Property_Stores
>  WHERE Property_Stores.OWNER_ID=1
>    AND Property.STORE_ID=Property_Stores.STORE_ID
> 
> But when it executes I get the following exception raised.
> 
> org.apache.torque.TorqueException: Only 1 columns exist!
> 
> I'm trying to count the number of rows that would be returned by the query
> rather than actually retrieving the records from the database.

Torque is expecting all columns to be present in the results so it can 
map the results to objects. For this purpose, you need to use

List results = PropertyPeer.doSelectVillageRecords(crit);

Look at the Village docs for more info.

Eric

RE: Question regarding Peers, Joins and Criteria

Posted by Derek Hardy <de...@teknosys.co.uk>.

Thanks for your assistance Eric.

I wonder if I could trouble you for one further piece of assistance that
would also answer an earlier query for me.

Using your code below, I added a new line

Criteria crit = new Criteria();
crit.addSelectColumn("count("+PropertyPeer.PROPERTY_ID+")");   // New line
added by DH
crit.addJoin(PropertyPeer.STORE_ID, PropertyStoresPeer.STORE_ID);
crit.add(PropertyStoresPeer.OWNER_ID, 1);
List results = PropertyPeer.doSelect(crit);

I get exactly the SQL I am trying to produce

SELECT count(Property.PROPERTY_ID)
  FROM Property, Property_Stores
 WHERE Property_Stores.OWNER_ID=1
   AND Property.STORE_ID=Property_Stores.STORE_ID

But when it executes I get the following exception raised.

org.apache.torque.TorqueException: Only 1 columns exist!

I'm trying to count the number of rows that would be returned by the query
rather than actually retrieving the records from the database.

Thanks again

Derek Hardy

-----Original Message-----
From: Eric Emminger [mailto:eric@ericemminger.com]
Sent: 13 May 2003 18:06
To: Turbine Torque Users List
Subject: Re: Question regarding Peers, Joins and Criteria


Derek

Derek Hardy wrote:
> Hi
>
> I'm trying to create a method in the PropertyPeer to reproduce the
following
> SQL query
>
> select *
>   from property p
>       ,property_stores s
>  where p.store_id = s.store_id
>    and s.owner_id = 1;
>
> How do I define the criteria to add a restriction on the join table.

Probably something like

Criteria crit = new Criteria();
crit.addJoin(PropertyPeer.STORE_ID, PropertyStoresPeer.STORE_ID);
crit.add(PropertyStoresPeer.OWNER_ID, 1);
List results = PropertyPeer.doSelect(crit);

Eric


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

Re: Question regarding Peers, Joins and Criteria

Posted by Eric Emminger <er...@ericemminger.com>.

Derek

Derek Hardy wrote:
> Hi
> 
> I'm trying to create a method in the PropertyPeer to reproduce the following
> SQL query
> 
> select *
>   from property p
>       ,property_stores s
>  where p.store_id = s.store_id
>    and s.owner_id = 1;
> 
> How do I define the criteria to add a restriction on the join table.

Probably something like

Criteria crit = new Criteria();
crit.addJoin(PropertyPeer.STORE_ID, PropertyStoresPeer.STORE_ID);
crit.add(PropertyStoresPeer.OWNER_ID, 1);
List results = PropertyPeer.doSelect(crit);

Eric