On Apr 4, 1:01 pm, ged <[email protected]> wrote:
> On Apr 4, 10:06 am, Henry Flower <[email protected]> wrote:
>
> > I want to add a column 'votes_up' (the total number of upvotes from all
> > users) to a query result:
>
> > SELECT *, (
> > SELECT COUNT(*)
> > FROM marks
> > WHERE ((marks.checklist_id = checklists.id)
> > AND (vote = 1))
> > ) votes_up
> > FROM checklists
> > WHERE blah-blah
>
> > This works, but is it possible to translate that SQL into Sequel's DSL?
>
> I got everything but the 'count(*)' of the sub-select:
>
> irb(main):001:0> db = Sequel.sqlite
> => #<Sequel::SQLite::Database: "sqlite:/">
>
> irb(main):002:0>
> db[:checklists].select_append( db[:marks].filter( :marks__checklist_id
> => :checklists__id ).filter(:vote => 1).as(:votes_up) )
> => #<Sequel::SQLite::Dataset: "SELECT *, (SELECT * FROM `marks` WHERE
> ((`marks`.`checklist_id` = `checklists`.`id`) AND (`vote` = 1))) AS
> 'votes_up' FROM `checklists`">
>
> If you add a `.count` at the end (which is how you'd make it a 'select
> count(*)' by itself, it tries to execute the subselect instead. I'm
> interested to know how to get a count(*) into a sub-select now, too.
How about this?
ruby-1.8.7-p334 :001 >
DB[:checklists].select_append( DB[:marks].select{count(:*)}.filter(
:marks__checklist_id
=> :checklists__id ).filter(:vote => 1).as(:votes_up) )
=> #<Sequel::Dataset: "SELECT *, (SELECT count(*) FROM marks WHERE
((marks.checklist_id = checklists.id) AND (vote = 1))) AS votes_up
FROM checklists">
--
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.
