You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Christian Grobmeier <gr...@gmail.com> on 2011/11/06 17:04:40 UTC
Selecting relationships without restriction
Hi all,
I have two tables Article and Price.
The table Price can containt historic prices which should not be selected.
I do something like that:
Expression expression = ExpressionFactory.matchExp("prices.historic", false);
Expression fullExp =
expression.orExp(ExpressionFactory.matchExp("prices+.historic",
null));
SelectQuery query = new SelectQuery(Module.class, fullExp);
List list = ctx.performQuery(query);
This generates a correct sql like:
SELECT DISTINCT t0....*
FROM articles t0
LEFT JOIN prices t1 ON (t0.id = t1.article_id)
WHERE (t1.historic = false) OR (t1.historic IS NULL)
Using that sql works well on my MySQL db directly.
When I run Cayenne, my junit test case fails because I when I call
getPrices() I get even the historic ones.
For example:
List list = ctx.performQuery(query);
Article a = (Article)list.get(0);
a.getPrices(); // <--- returns historic true/false
I had the expectation that the historic prices should not appear here.
Do I miss something?
Cheers,
Christian
Re: Selecting relationships without restriction
Posted by Aristedes Maniatis <ar...@maniatis.org>.
On 7/11/11 3:04 AM, Christian Grobmeier wrote:
> Hi all,
>
> I have two tables Article and Price.
> The table Price can containt historic prices which should not be selected.
>
> I do something like that:
>
> Expression expression = ExpressionFactory.matchExp("prices.historic", false);
> Expression fullExp =
> expression.orExp(ExpressionFactory.matchExp("prices+.historic",
> null));
> SelectQuery query = new SelectQuery(Module.class, fullExp);
> List list = ctx.performQuery(query);
>
> This generates a correct sql like:
>
> SELECT DISTINCT t0....*
> FROM articles t0
> LEFT JOIN prices t1 ON (t0.id = t1.article_id)
> WHERE (t1.historic = false) OR (t1.historic IS NULL)
>
> Using that sql works well on my MySQL db directly.
>
> When I run Cayenne, my junit test case fails because I when I call
> getPrices() I get even the historic ones.
>
> For example:
>
> List list = ctx.performQuery(query);
> Article a = (Article)list.get(0);
> a.getPrices(); //<--- returns historic true/false
>
> I had the expectation that the historic prices should not appear here.
> Do I miss something?
>
> Cheers,
> Christian
You could use single table inheritance to map this, if that isn't overkill for your needs. Then you get a discriminator which will be applied to the joins.
Ari
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: Selecting relationships without restriction
Posted by Michael Gentry <mg...@masslight.net>.
Hi Christian,
As others pointed out, getPrices() will return all of the prices.
What I usually do for things like this is write a helper method to
filter the historic ones out. Create a getCurrentPrices() which
returns calls getPrices() and filters based upon historic = true and
return a new list.
mrg
On Sun, Nov 6, 2011 at 11:04 AM, Christian Grobmeier
<gr...@gmail.com> wrote:
> Hi all,
>
> I have two tables Article and Price.
> The table Price can containt historic prices which should not be selected.
>
> I do something like that:
>
> Expression expression = ExpressionFactory.matchExp("prices.historic", false);
> Expression fullExp =
> expression.orExp(ExpressionFactory.matchExp("prices+.historic",
> null));
> SelectQuery query = new SelectQuery(Module.class, fullExp);
> List list = ctx.performQuery(query);
>
> This generates a correct sql like:
>
> SELECT DISTINCT t0....*
> FROM articles t0
> LEFT JOIN prices t1 ON (t0.id = t1.article_id)
> WHERE (t1.historic = false) OR (t1.historic IS NULL)
>
> Using that sql works well on my MySQL db directly.
>
> When I run Cayenne, my junit test case fails because I when I call
> getPrices() I get even the historic ones.
>
> For example:
>
> List list = ctx.performQuery(query);
> Article a = (Article)list.get(0);
> a.getPrices(); // <--- returns historic true/false
>
> I had the expectation that the historic prices should not appear here.
> Do I miss something?
>
> Cheers,
> Christian
>
Re: Selecting relationships without restriction
Posted by Christian Grobmeier <gr...@gmail.com>.
Hi Joe,
thanks for letting me know. This would be a killer feature.
Cheers,
Christian
On Sun, Nov 6, 2011 at 5:35 PM, Joseph Senecal <se...@apple.com> wrote:
> Christian,
>
> There is no way that I know of to specify additional qualifiers to Cayenne's built in relationships. The design is that getPrices should return all prices where Price.article field has been set to that article.
>
> What you can do is add your own method getCurrentPrices that returns all non-historic prices. This could either explicitly run a query looking for Article.articleID = Price.articleID and Price.historic is false or null. Or you can filter though the list of all Prices to select the prices which aren't historic.
>
> The second option has the advantage that it will automatically notice when new prices are added or old prices are marked as historic.
>
> The first option has the advantage that it doesn't load all prices into memory, but it doesn't return unsaved current prices. And if the result is cached, it doesn't reflect any changes.
>
> Joe
>
> On Nov 6, 2011, at 8:04 AM, Christian Grobmeier wrote:
>
>> Hi all,
>>
>> I have two tables Article and Price.
>> The table Price can containt historic prices which should not be selected.
>>
>> I do something like that:
>>
>> Expression expression = ExpressionFactory.matchExp("prices.historic", false);
>> Expression fullExp =
>> expression.orExp(ExpressionFactory.matchExp("prices+.historic",
>> null));
>> SelectQuery query = new SelectQuery(Module.class, fullExp);
>> List list = ctx.performQuery(query);
>>
>> This generates a correct sql like:
>>
>> SELECT DISTINCT t0....*
>> FROM articles t0
>> LEFT JOIN prices t1 ON (t0.id = t1.article_id)
>> WHERE (t1.historic = false) OR (t1.historic IS NULL)
>>
>> Using that sql works well on my MySQL db directly.
>>
>> When I run Cayenne, my junit test case fails because I when I call
>> getPrices() I get even the historic ones.
>>
>> For example:
>>
>> List list = ctx.performQuery(query);
>> Article a = (Article)list.get(0);
>> a.getPrices(); // <--- returns historic true/false
>>
>> I had the expectation that the historic prices should not appear here.
>> Do I miss something?
>>
>> Cheers,
>> Christian
>
>
--
http://www.grobmeier.de
Re: Selecting relationships without restriction
Posted by Joseph Senecal <se...@apple.com>.
Christian,
There is no way that I know of to specify additional qualifiers to Cayenne's built in relationships. The design is that getPrices should return all prices where Price.article field has been set to that article.
What you can do is add your own method getCurrentPrices that returns all non-historic prices. This could either explicitly run a query looking for Article.articleID = Price.articleID and Price.historic is false or null. Or you can filter though the list of all Prices to select the prices which aren't historic.
The second option has the advantage that it will automatically notice when new prices are added or old prices are marked as historic.
The first option has the advantage that it doesn't load all prices into memory, but it doesn't return unsaved current prices. And if the result is cached, it doesn't reflect any changes.
Joe
On Nov 6, 2011, at 8:04 AM, Christian Grobmeier wrote:
> Hi all,
>
> I have two tables Article and Price.
> The table Price can containt historic prices which should not be selected.
>
> I do something like that:
>
> Expression expression = ExpressionFactory.matchExp("prices.historic", false);
> Expression fullExp =
> expression.orExp(ExpressionFactory.matchExp("prices+.historic",
> null));
> SelectQuery query = new SelectQuery(Module.class, fullExp);
> List list = ctx.performQuery(query);
>
> This generates a correct sql like:
>
> SELECT DISTINCT t0....*
> FROM articles t0
> LEFT JOIN prices t1 ON (t0.id = t1.article_id)
> WHERE (t1.historic = false) OR (t1.historic IS NULL)
>
> Using that sql works well on my MySQL db directly.
>
> When I run Cayenne, my junit test case fails because I when I call
> getPrices() I get even the historic ones.
>
> For example:
>
> List list = ctx.performQuery(query);
> Article a = (Article)list.get(0);
> a.getPrices(); // <--- returns historic true/false
>
> I had the expectation that the historic prices should not appear here.
> Do I miss something?
>
> Cheers,
> Christian