COUNT statement

[ghost]

this can be done with the SELECT function, but a high level function can ease the effort of doing this, will do PR if you want something like this

[sockeqwe]

I'm not sure how to add that into the SELECT function easily. How would you do that?

[ghost]

Okay on the risk of making a joke of my sql knowledge the count function is done by SELECT COUNT(column names) FROM TABLE NAME right... implementing this with the current SELECT function in the Dao class is pretty straightforward, it's just some sugar coating for the api On 25-Sep-2015 12:03 am, "Hannes Dorfmann" notifications@github.com wrote:

I'm not sure how to add that into the SELECT function easily. How would you do that?

— Reply to this email directly or view it on GitHub https://github.com/sockeqwe/sqlbrite-dao/issues/16#issuecomment-143013445 .

[ghost]

I think with the current SELECT function it will be like SELECT("COUNT(*)").FROM... On 25-Sep-2015 12:06 am, "Aashrai Ravooru" ashrair@gmail.com wrote:

Okay on the risk of making a joke of my sql knowledge the count function is done by SELECT COUNT(column names) FROM TABLE NAME right... implementing this with the current SELECT function in the Dao class is pretty straightforward, it's just some sugar coating for the api On 25-Sep-2015 12:03 am, "Hannes Dorfmann" notifications@github.com wrote:

I'm not sure how to add that into the SELECT function easily. How would you do that?

— Reply to this email directly or view it on GitHub https://github.com/sockeqwe/sqlbrite-dao/issues/16#issuecomment-143013445 .

[sockeqwe]

yeah, but actually that would be:

SELECT("COUNT(*)").FROM() ...

and if I will select other things as well:

SELECT("COUNT(*), column1, colum2").FROM() ...

or with AS to name the COUNT column result:

SELECT("COUNT(*) AS foo, column1, colum2").FROM() ...

I'm not sure how this could be expressed?

[ghost]

i primarily see COUNT as a function in Dao that just returns the count and not to be used with other columns, so maybe something like

public SELECT COUNT(String column){
return SELECT("COUNT("+coumn+")");
}

Didn't want to do all that String appending, especially since the other SQL functions in the Dao are so convenient, maybe just me feel free to ignore this if you feel its unnecessary

[sockeqwe]

I understand what you mean, but it to me, personally, this SQL grammar seems to be wrong since sql COUNT() is a function like MAX(), MIN(), AVG(), SUM(), etc. and can be used for selecting like any other column and in combination with any arbitrary other function or column name.

I personally prefer correct SQL grammar, even if that means to deal with string concatenation.

[ghost]

Hmm one interesting thing maybe if we can chain functions like these to SELECT call, although it does take away the freedom of deciding on the order of the sql functions

[ghost]

Something like SELECT(columns...).COUNT().MAX(). FROM()

[ghost]

Although not sure if this won't be misleading as to say that these are not SQL functions but something that the api invented

[sockeqwe]

yes, something like that could work, but sometimes the order of selecting columns (or functions matters).

I think more about something like this: Changing definition of SELECT(String ... columns) to SELECT(CharSequence ... columns). Since CharSequence is a interface implemented by String we could also provide our own "classes" like COUNT implements CharSequence like this

class COUNT implements CharSequence {

   private String columnName;
   private String as;

   public COUNT(String columnName){
        this.columnName = columnName;
    }

   public COUNT(String columnName, String as){
       this(columnName);
       this.as = as;
   }

   @Override
   public void toString(){
       if (as != null)
          return "COUNT("+columName+") AS "+as;

       return "COUNT("+columnName+");
   }
}

Then you could use it as before:

SELECT("column1", COUNT("column2"), "column3").FROM() ...

[ghost]

Yes that's it this is much better it will make the code look less ugly especially when the number of SQL functions used are too many and also give the freedom of ordering On 25-Sep-2015 12:56 am, "Hannes Dorfmann" notifications@github.com wrote:

yes, something like that could work, but sometimes the order of selecting columns (or functions matters) ...

I think more about something like this: Changing definition of SELECT(String ... columns) to SELECT(CharSequence ... columns). Since CharSequence is a interface implemented by String we could also provide out own "classes" like COUNT implements CharSequence like this

class COUNT {

private String columnName; private String as;

public COUNT(String columnName){ this.columnName = columnName; }

public COUNT(String columnName, String as){ this(columnName); this.as = as; }

@Override public void toString(){ if (as != null) return "COUNT("+columName+") AS "+as;

   return "COUNT("+columnName+");   }}

Then you could use it as before:

SELECT("column1", COUNT("column2"), "column3").FROM() ...

— Reply to this email directly or view it on GitHub https://github.com/sockeqwe/sqlbrite-dao/issues/16#issuecomment-143025295 .

[sockeqwe]

But before spending more time on that (there are quite a lot functions) I want to know in which direction SQLBrite will go (adding first party implementation for SQL grammar) https://github.com/square/sqlbrite/issues/32

[sockeqwe]

There is a much simpler solution for that: Simply add a method to Dao base class that retruns a string, something like this:

class Dao {
   ...
  protected String COUNT(String columns){
     return "COUNT("+column+")";
}

Then in your MyDao

class MyDao extends Dao {
   public Observable<FooCount> getFooCount(){
        return query( SELECT ("col1", COUNT("*"), "col2").FROM("Foo") ...
   }
}