cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nikita Timofeev <ntimof...@objectstyle.com>
Subject Re: API for aggregate and non aggregate SQL functions
Date Mon, 09 Jan 2017 09:45:40 GMT
On Sun, Jan 8, 2017 at 3:26 PM, Aristedes Maniatis <ari@maniatis.org> wrote:
> On 8/1/17 1:03am, Nikita Timofeev wrote:
>
>> The later is closer to the design.
>> Both column() and columns() methods return same ObjectSelect object,
>> they only change result type (as fetchDataRows() method do):
>> column() changes result to ObjectSelect<T>, where T is type of
>> Property, so no cast of the result required
>> and columns() changes to ObjectSelect<Object[]>. The idea is that you
>> can still use all methods in ObjectSelect.
>
> Yes, fair enough and that makes sense. So could we do this:
>
> List<Object[]> namesAndBirthDates = ObjectSelect.query(Artist.class)
>               .column(Artist.ARTIST_NAME)
>               .column(Artist.ARTIST_BIRTH_DATE)
>               .select(context);
>
> how can we handle the result type?
>
> Will this work with selectOne() returning Object or Object[] in difference cases?
>
>>> I know factories are the Java way, but given how common count() and max() are,
surely we'd want a shortcut. I'd like to be able to go directly to:
>>>
>>> int count = ObjectSelect.query(Artist.class)
>>>                 .where(Artist.ARTIST_NAME.eq("Ari"))
>>>                 .selectCount(context);
>>>
>> I want this shortcut too :) I'd like to avoid a feature bloat, so that
>> could be done later.
>
>
> I think its worth planning the API for this now, given that this is probably the most
common function used.

With my current implementation column() method overrides result with
each successive call (in your example the result will be List<Date>
without name column)
in order to provide exact return type, whereas columns() method always
returns Object[] result.

But your feedback is pushing me towards the solution that I didn't
want to use initially:
to introduce some abstract FluentSelect with descendants like
ObjectSelect and ColumnSelect.
Where ColumnSelect can be used directly or can be created within
column() or columns() method call in ObjectSelect (as you suggested in
the first place).

Additional benefit of this new query that it can provide a nice
shortcut for the COUNT(*) query:

long count = ColumnSelect.query(Artist.class, Property.COUNT)
        .where(Artist.ARTIST_NAME.like("artist%"))
        .selectOne(context);

And here is example for several properties:

Object[] nameAndDate = ColumnSelect.query(Artist.class,
Artist.ARTIST_NAME, Artist.DATE_OF_BIRTH)
        .orderBy(Artist.ARTIST_NAME.asc())
        .selectFirst(context);

or

Object[] nameAndDate = ColumnSelect.query(Artist.class)
        .columns(Artist.ARTIST_NAME)
        .columns(Artist.DATE_OF_BIRTH)
        .orderBy(Artist.ARTIST_NAME.asc())
        .selectFirst(context);

Do you think that this is a more clear way of doing columns queries?
Or it is excessive for the purpose of avoiding limitation of multiple
column() method calls?

>
>> Property<Integer> nameLength =
>> Property.create(FunctionExpressionFactory.lengthExp(Artist.ARTIST_NAME.path()),
>> Integer.class);
>> List<Artist> artists = ObjectSelect.query(Artist.class)
>>                                .where(nameLength.gt(10))
>>                                .select(context);
>
> Could that syntax above could be cleaner like:
>
>    Property<Integer> nameLength = Artist.ARTIST_NAME.function(Expression.SQL_LENGTH)
>
> or is that just going to result in a whole lot of clutter in the class?
>

Such methods will create too much coupling between properties and
functions, I'm afraid,
as Property class would know about all SQL functions and their arguments.

>
> List<Object[]> result2 = ObjectSelect.query(Artist.class)
>                  .columns(Artist.ARTIST_NAME, Property.HOLLOW_OBJECT, paintingCountProperty)
>                  .having(paintingCountProperty.gt(10L))
>                  .select(context);
>
> This sort of query would be great for displaying a list of records (where you might want
only a couple of columns to be loaded and displayed rather than whole objects). But then double
clicking opens an edit view and you want to easily fault all its attributes without worrying
about tracking its PK directly.
>

Yes, it would be really nice to have this feature. But as I mentioned
the exact implementation is out of scope for now.

>
> Finally, 'having()' matches the SQL (HAVING is needed for queries on the result of functions).
Have you thought about how that could be abstracted away so that the user can use where()
and the correct SQL is still generated by Cayenne?
>

I can't prove but I think there are some cases when the final result
depends on whether you use where() or having() on column.
Something like this (though in this exact case or() method can still
trace the difference):

ObjectSelect.query(Artist.class)
     .columns(Artist.ARTIST_NAME, artistCount)
     .where(Artist.ARTIST_NAME.like("a%"))
     .having(artistCount.gt(2L))
     .select(context)

vs

ObjectSelect.query(Artist.class)
     .columns(Artist.ARTIST_NAME, artistCount)
     .having(Artist.ARTIST_NAME.like("a%"))
     .or(artistCount.gt(2L)) // this goes to having
     .select(context)

But more important that it's really hard to find whether expression
can be used in having() or with where() clause as this requires to
trace every expression
backwards to its db attribute before assembling the final SQL.
So in this case I vote for the simplicity.

-- 
Best regards,
Nikita Timofeev

Mime
View raw message