We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Falcon group by help

I am trying to make a stats script, that groups by date and and unique_id, to see what if users have visit before

My database exampel:

2015-05-01 - 123

2015-05-01 - 123

2015-05-01 - 000

== 2015-05-01 is the date and uniques is 2

2015-05-02 - 123

2015-05-02 - 123

2015-05-02 - 123

== 2015-05-02 is the date and uniques is 1

My code is this:

$get_unique = $this->modelsManager->createBuilder()
        ->columns(array('COUNT(*) as cnt', 'Statistics.date', 'Statistics.unique_id'))
        ->from('Statistics')
        ->groupBy(array('Statistics.date, Statistics.unique_id'))
        ->distinct('Statistics.unique_id')
        ->betweenWhere('Statistics.date', $from_date, $to_date)
        ->getQuery()
        ->execute();

Hope you understand and can help me out! Thanks!



85.5k

i udnerstand, but i dont see where did you explain the problem ?

Cheers



40.6k

The problem is that it dosent work. Either it is grouping by date or unique id, depends on what i put first - In this example it is date that comes first..

So the problem is am i doing this right or is there something i do wrong?



85.5k
edited Nov '15 
SELECT * 
  FROM `Statistics`
 WHERE id IN (
               SELECT uniques 
                 FROM `Statistics`
                 WHERE
                 Statistics.date > 01-01-2014 
                 AND Statistics.date < 12-12-2015 
                 group by uniques
             );

i can't understand your db structure correctly, if this doesnt help you, please provide more info of what database is ( simple, just the important columns ) and what do you want to see as result



40.6k
edited Nov '15

I need it to be like this:

2015-10-19 = 2 unique_ids

2015-10-14 = 1 unique_ids

I need it to group every date, and then count how many diffrent unique_ids there is in every date

Hope you understand!



4.7k

If you table structure is pretty much like this try:

SELECT date, count(id) FROM Statistics 
    WHERE
                 Statistics.date > 01-01-2014 
                 AND Statistics.date < 12-12-2015 
                 GROUP BY date


40.6k

But that still dosent group my unique_ids

I need to count how many diffrent unique_ids there is, if a unique_id is the same 3 times it only counts as 1



4.7k

Should just need to add that group by. GROUP BY date, uniquie_id



40.6k

Then why is it that my code dosent work, that i provided at my first post?

It seems like it only groupes by the first column that i enter - And i have tryed array(array('date', 'unique_id'))



85.5k

do you have skype



2.2k
edited Nov '15

Check your code, this may be a typo.

You posted: ->groupBy(array('Statistics.date, Statistics.unique_id'))

but you may need ->groupBy(array('Statistics.date', 'Statistics.unique_id'))

Note the extra ' around the , in the array, giving you two array items, instead of one. Try that.

Edit: Oh, looks like you did try that... =P Try running a ->getGroupBy() on the query object and see what you get.

Edit2: Oh, it looks like you may have tried array(array('date','unique_id'))... Did you really have the extra array() around it inside the ->groupBy()?



40.6k

Thanks for the help everyone :-)

No i dont have an extra array arround, it was just a type error :-)

I can see that my SQL is correct and i even tried with raw SQL..



-17

This work for me. I have applied some code lines in it.