CODEWITHSUNDEEP
mysqlrubysequel
theGreenCabbage
theGreenCabbage

Reputation: 4845

Sequel JOIN and WHERE

I have the following MySQL statement:

SELECT
    a.id,
    b.category,
    b.message,
    b.is_valid
FROM
    "NODE" a,
    "SYSTEM_STATUS" b
WHERE 
    ("LAST_HEARTBEAT" > TIMESTAMP '2017-09-18 15:10:04.471826') AND 
    a.id = b.node_id

I want to convert the above to Sequel

I have this method which is a reduce version of the above MySQL statement:

0> self.class.live_nodes

SELECT "ID" FROM "NODE" WHERE ("LAST_HEARTBEAT" > TIMESTAMP '2017-09-18 15:11:12.906017')

=> [#<MyApp::Model::Node @values={:id=>348}>]

Now, using the results of this method, I want to do a join with a table called system_status, where the results of node.id = system_status.node_id.

Next, I want to take the results of live_nodes and join it to system_status where the result is equal to system_status.node_id, and I am not sure how to do it.

I tried the following: 

0> MyApp::Model::Node.where { last_heartbeat > Time.now - HEARTBEAT_TIMEOUT.seconds }.join(:system_status, :node_id, :id)

But received this error

=> no implicit conversion of Symbol into Integer

Not sure how to fix it.. I also don't think I am doing the aliasing correctly

Upvotes: 1

Views: 2696

Answers (1)

engineersmnky
engineersmnky

Reputation: 29373

You are extremely close with the join but I believe it should be 

# basically table_name, left_side, right_side
join(:system_status, node_id: :id)

For more explicit joins the following should also work

# Sequel > 4.38.0
join(:system_status, node_id: Sequel[:nodes][:id])
# Sequel < 4.39.0
join(:system_status, node_id: Sequel.expr(:nodes__id))

This should generate 

INNER JOIN system_status ON system_status.node_id = nodes.id

Upvotes: 2

Related Questions