On Jan 15, 10:51 am, Joe <[email protected]> wrote:
> hi,
> so i am having a related problem, i currently use your suggestions
> like so...
>
> test = DB[:New_Stats].filter(:src_file_id => all_src_files).where
> (:Experiment_ID => 2).group_and_count
> (:src_file_id, :Channel, :Table_id).having{count > 1}
>
> which gives me a resulting dataset containing,
> {:Channel, :count, :Table_id, :src_file_id}
>
> what i would like is to be able to get the Stat_ID (the unique primary
> key of :New_Stats)
>
> essentially some thing like this (when expressed in SQL )
>
> SELECT `Stat_ID` , `src_file_id` , `Channel` , `Table_id` , count( * )
> AS `count`
> FROM `New_Stats` WHERE ( ( `src_file_id` IN ( SELECT `id` FROM
> `src_files_table` WHERE ( `src_camera` =2 ) ) ) AND ( `Experiment_ID`
> =2 ) ) GROUP BY `src_file_id` , `Channel` , `Table_id` HAVING
> ( `count` >1 )
>
> but when i modify my expression above to say something like
>
> test = DB[:New_Stats].filter(:src_file_id => all_src_files).where
> (:Experiment_ID => 2).select
> (:src_file_id, :Channel, :Table_id, :Stat_ID).group_and_count
> (:src_file_id, :Channel, :Table_id).having{count > 1}
>
> the returned data still doesnt include the Stat_ID column, where do i
> put the select statement to pull out this unique column, so its not
> included in the group_and_count function, but is still returned in the
> dataset ?
What you want is not possible (from a relational perspective, it makes
no sense). There are going to be multiple Stat_ID values for each row
in the dataset, so which one do you want? If you want just the lowest
or highest, use :min.sql_function(:Stat_ID) or :max.sql_function
(:Stat_ID).
test = DB[:New_Stats].
filter(:src_file_id => all_src_files).
where(:Experiment_ID => 2).
group_and_count(:src_file_id, :Channel, :Table_id).
select_more(:min.sql_function(:Stat_ID).as(:Stat_ID)).
having{count > 1}
If you want all of them, you'll have to use a separate dataset:
test = DB[:New_Stats].
filter(:src_file_id => all_src_files).
where(:Experiment_ID => 2).
group_and_count(:src_file_id, :Channel, :Table_id).
having{count > 1}
stat_ids = DB[:New_Stats].
filter([:src_file_id, :Channel, :Table_id]=>test.from_self.select
(:src_file_id, :Channel, :Table_id)).
select_map(:Stat_ID)
> i can of course just use the SQL i have worked out, but it would be
> nice to keep things in the sequel style which is really great!
If that SQL works, it's probably MySQL specific syntax. I'm pretty
sure that PostgreSQL would complain about it, as would other databases
that require that select queries that use group by have all the select
clause items that aren't being grouped by to be aggregate functions.
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
