Ruby Sequel left outer join query

Question

I have a more complex query (does not work)

query.select_all(:table_1).select_more(:col_1)
.left_outer_join(:table_2, table_1_id: :id, col_1: 1, 
Seqel.lit('col_2 > ?', 1.week.ago)

I need to compare col_2 like this:

"col_2 > '2018-01-01 10:10:10'"

it is easy to write whole query as custom sql, but I would like to chain methods and so far I couldn't succeed in adding the above.

expected result ~:

"SELECT `table_1`.*, `table_2`.`col_1` FROM `table_1`
LEFT OUTER JOIN `table_2` 
ON (
`table_2`.`table_1_id` = `table_1`.`id`
AND
`table_2`.`col_1` = 1 
AND 
`table_2`.`col_2` > '2018-01-01 10:10:10')"

---

I used with_sql but it ignores other scopes. Also I tried:

 .left_outer_join("`table_2` ON (`table_2`.`table_1_id` =
 `table_1`.`id` AND `table_2`.`col_1` = 1 AND `table_2`.`col_2` >
 '2017-09-28 06:49:53 UTC')")

Problem in last one is that it wraps that whole string with extra ``

---

what worked for me:

.left_outer_join(:table_2, 
Sequel.lit('table_2.table_1_id = table_1.id AND table_2.col_1 = ? 
AND table_2.col_2 > ?', id, date))

but is there a way to accomplish it without using string queries?

• Pramod Solanky: Why not execute that sql in #find_by_sql method of rails ?
• zombie_ghast: I could use it but I need to chain more scopes. find_by_sql does not seem to work with sequel.

Answer 1

.left_outer_join(:table_2, :table_1_id=>:id, :col_1=>id){Sequel[:table_2][:col_2] > date}

• Alexander: While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value.

Answer 2

I will post my own answer, but will not accept it because I think it could be done better.

.left_outer_join(:table_2, 
Sequel.lit('table_2.table_1_id = table_1.id AND table_2.col_1 = ? 
AND table_2.col_2 > ?', id, date))

So waiting for other suggestions.

• lacostenycoder: you should be able to chain AND statements with .where() see my updated answer

Answer 3

This is might be a bit cleaner:

.left_outer_join(:table_2,
 table_2.where(table_1_id: 'table_1.id')
 .where(col_1: id)
 .where('col 2 > ?', date)
 )

• zombie_ghast: it does not change much
• lacostenycoder: what is query defined as in your example? Can you paste the output of your current example and the one I posted?
• zombie_ghast: actually I put this as second argument and put inside rest of string query and it worked: .left_outer_join(:table_2, Sequel.lit('table_2.table_1_id = table_1.id AND table_2.col_1 = ? AND table_2.col_2 > ?', id, date)), it 's cool it worked but would be nice to have more sequel-like solution
• lacostenycoder: I updated my answer, but glad you figured it out., you should post your solution too.