3

Is it possible to use sequel to execute a query like this:

select (select count(*) from users where blah = 'blah') as "users",
       (select count(*) from contacts where blah = 'blah') as "contacts"

I know I can execute these queries one a time using sequel, but I would like to execute them all at the same time.

dagda1
  • 26,856
  • 59
  • 237
  • 450

4 Answers4

5

You can do that query without writing raw SQL with the following:

dataset = DB.select {[ 
  DB[:users].where(blah: 'blah').select { count('*') }.as(:users),
  DB[:contacts].where(blah: 'blah').select { count('*') }.as(:contacts) 
]}

dataset.first
# => { users: X, contacts: Y }

dataset.sql
# => "SELECT (SELECT count('*') FROM \"users\" WHERE (\"blah\" = 'blah')) AS \"users\", 
#            (SELECT count('*') FROM \"contacts\" WHERE (\"blah\" = 'blah')) AS \"contacts\""
hjing
  • 4,922
  • 1
  • 26
  • 29
4

Yes you can do it ok with the sequel gem.

require 'sequel'

DB = Sequel.sqlite # memory database

DB.create_table :users do
  primary_key :id
  String :name
end

users = DB[:users] # Create a dataset
users.insert(:name => 'jim')

DB.create_table :contacts do
  primary_key :id
  String :name
end
contacts = DB[:contacts] # Create a dataset
contacts.insert(:name => 'first')


DB['select (select count(*) from users where name = "jim") as users, 
           (select count(*) from contacts where name = "first") as contacts'].each do |row|
  puts row
end


#{:users=>1, :contacts=>1}

It should be noted however that it is not a great idea to include raw strings in a sequel method. They can be extracted as follows:

DB['select (select count(*) from users where name = ?) as users, 
           (select count(*) from contacts where name = ?) as contacts, 'jim', 'first'].each do |row|
  puts row
end

Also as noted in another answer you could express this query completely without resorting to SQL which is more in the spirit of the module. :

dataset = DB.select {[
  DB[:users].where(name: 'jim').select { count('*') }.as(:users),
  DB[:contacts].where(name: 'first').select { count('*') }.as(:contacts)
]}

dataset.sql

# SELECT (SELECT count('*') FROM `users` WHERE (`name` = 'jim')) AS 'users', (SELECT count('*') FROM `contacts` WHERE (`name` = 'first')) AS 'contacts'

dataset.first

# {:users=>1, :contacts=>1}
John C
  • 4,276
  • 2
  • 17
  • 28
  • Whilst your example works, it is not a good example of sequel nor in the spirit of the gem using raw strings when sequel methods can and should be used to compose the query. – Andrew Hacking May 24 '14 at 02:01
  • @AndrewHacking yes but the question specifically asked if that query was possible. – John C May 24 '14 at 02:05
  • There is a continuum of good to bad answers and I think the other answer by @hjing is typical of how you compose queries in sequel whilst yours is not representative of the normal approach. Since SO is read by many people it is important that the best answers rise to the top and the not so good fall to the bottom hence my downvote and explanation. – Andrew Hacking May 24 '14 at 02:59
  • Good point. I have tried again to improve my answer. I have learned a lot from this question. – John C May 24 '14 at 03:19
  • I upvoted now that your answer illustrates both 'dirty' and clean approaches but the points should probably be shared between yourself and @hjing if that's possible. – Andrew Hacking May 24 '14 at 03:49
1

You can execute as you mentioned above, there is no issue with your query . I have executed the same and it was executed without any issues ..

below was what i have executed 

     select( SELECT count(*) FROM `abcde` where `User_ID`=4001) as "aa",
     (SELECT count(*) FROM `abcdef` where `User_ID`=4018) as "bbb"

results : 

   aa   bbb
   181  364
user3580608
  • 48
  • 5
0

you need to add * from after select and may be some parenthesis: 

select * from ((select count(*) from users where blah = 'blah') as "users",
   (select count(*) from contacts where blah = 'blah') as "contacts")
user1928596
  • 1,503
  • 16
  • 21