FAQ
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

Search Discussions

  • Joseph Senecal at Nov 6, 2011 at 4:35 pm
    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
  • Christian Grobmeier at Nov 6, 2011 at 5:37 pm
    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 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
  • Aristedes Maniatis at Nov 6, 2011 at 10:01 pm

    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
  • Michael Gentry at Nov 7, 2011 at 2:21 pm
    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
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedNov 6, '11 at 4:05p
activeNov 7, '11 at 2:21p
posts5
users4
websitecayenne.apache.org

People

Translate

site design / logo © 2022 Grokbase