You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Damon <mi...@gmail.com> on 2011/03/09 19:33:54 UTC

Query using a join table

I'm trying to perform a query using a join table.  I have read the documentation and several websites.  Im stuck

returns: List<Site>

String sql = "SELECT site.* from site, siteservice WHERE " +
                "site.id = siteservice.site_id AND " +
                "state_id = $stateID AND " +
                "siteservice.service_id = $serviceID";
        SQLTemplate query = new SQLTemplate(Site.class, sql);
        Map parameters = new HashMap();
        parameters.put("stateID", stateId);
        parameters.put("serviceID", serviceId);
        query.setParameters(parameters);
        return BaseContext.getThreadObjectContext().performQuery(query);

Table siteservice contains:
site_id
service_id

I can execute the query on the db and i get the results im expecting.  Is the SQLTemplate the correct type to use? What direction should i take?  


Thanks for the help.

Re: Query using a join table

Posted by Michael Gentry <mg...@masslight.net>.
Hi Damon,

I was going to point you to using Expression.fromString() since you
already have a Map of values, but then I realized your Map was just
holding primary keys (or at least that's my assumption).  Typically in
Cayenne you don't map primary keys or even care about them for the
most part (you'd care more about objects, etc).  Can you tell us a bit
more about what you are trying to do?

Thanks,

mrg


On Wed, Mar 9, 2011 at 1:33 PM, Damon <mi...@gmail.com> wrote:
> I'm trying to perform a query using a join table.  I have read the documentation and several websites.  Im stuck
>
> returns: List<Site>
>
> String sql = "SELECT site.* from site, siteservice WHERE " +
>                "site.id = siteservice.site_id AND " +
>                "state_id = $stateID AND " +
>                "siteservice.service_id = $serviceID";
>        SQLTemplate query = new SQLTemplate(Site.class, sql);
>        Map parameters = new HashMap();
>        parameters.put("stateID", stateId);
>        parameters.put("serviceID", serviceId);
>        query.setParameters(parameters);
>        return BaseContext.getThreadObjectContext().performQuery(query);
>
> Table siteservice contains:
> site_id
> service_id
>
> I can execute the query on the db and i get the results im expecting.  Is the SQLTemplate the correct type to use? What direction should i take?
>
>
> Thanks for the help.