Need help in SQL and Sequel involving inner join and where/filter

Question

Need help transfer sql to sequel:
SQL:

SELECT table_t.curr_id FROM table_t
INNER JOIN table_c ON table_c.curr_id = table_t.curr_id 
INNER JOIN table_b ON table_b.bic = table_t.bic
WHERE table_c.alpha_id = 'XXX' AND table_b.name='Foo';

I'm stuck in the sequel, I don't know how to filter, so far like this:

 cid= table_t.select(:curr_id).
                    join(:table_c, :curr_id=>:curr_id).
                    join(:table_b, :bic=>:bic).
                    filter( ????? )

Answer with better idiom than above is appreciated as well.Tnx.

UPDATE:
I have to modify a little to make it works

cid = DB[:table_t].select(:table_t__curr_id).
  join(:table_c, :curr_id=>:curr_id).
  join(:table_b, :bic=>:table_t__bic). #add table_t or else ERROR: column table_c.bic does not exist
  filter(:table_c__alpha_id => 'XXX',
         :table_b__name => 'Foo')

without filter,

cid = DB[:table_t].select(:table_t__curr_id).
                    join(:table_c, :curr_id=>:curr_id, :alpha_id=>'XXX').
                    join(:table_b, :bic=>:table_t__bic, :name=>'Foo')

btw I use pgsql 9.0

i dont want to open new question, just want to know how to make the last code above sql injection proof? — mhd, Jan 20 '11 at 06:16

score 7 · Accepted Answer · answered Dec 21 '10 at 16:47

This is the pure Sequel way:

cid = DB[:table_t].select(:table_t__curr_id).
  join(:table_c, :curr_id=>:curr_id).
  join(:table_b, :bic=>:bic).
  filter(:table_c__alpha_id => 'XXX',
         :table_b__name => 'Foo')

Note that you can also do this without a WHERE, since you are using INNER JOIN:

cid = DB[:table_t].select(:table_t__curr_id).
  join(:table_c, :curr_id=>:curr_id, :alpha_id=>'XXX').
  join(:table_b, :bic=>:bic, :name=>'Foo')

score 0 · Answer 2 · answered Dec 21 '10 at 14:37

0

I think you can always use something like

.filter('table_c.alpha_id = ? AND table_b.name = ?', 'XXX', 'Foo')

answered Dec 21 '10 at 14:37

Mladen Jablanović

43,461
10
90
113

score -1 · Answer 3 · answered Dec 21 '10 at 16:35

One thing to remember is that Sequel is plenty happy to let you use raw SQL. If you find it easier to express the query as SQL go ahead, just be sure to comment it so you can find it later. Then you can return to that line and adjust it so it's taking advantage of Sequel's awesomeness.

Try to avoid anything that is specific to a particular DBM though, because you'll be reducing portability which is one of the big reasons for using an ORM to generate the queries.

Need help in SQL and Sequel involving inner join and where/filter

3 Answers3