CODEWITHSUNDEEP
ruby-on-railspostgresql
Reddirt
Reddirt

Reputation: 5953

SQL not working for pg

I'm trying to use SQL to get information from a Postgres database using Rails.

This is what I've tried:

Select starts_at, ends_at, hours, employee.maxname, workorder.wonum from events where starts_at>'2018-03-14'
inner join employees on events.employee_id = employees.id
inner join workorders on events.workorder_id = workorders.id;

I get the following error:

ERROR: syntax error at or near "inner" LINE 2: inner join employees on events.employee_id = employees.id

Upvotes: 2

Views: 64

Answers (2)

Greg
Greg

Reputation: 6649

Sami's comment is correct, but since this question is tagged with ruby-on-rails you can try to use ActiveRecord's API to do the same:

Make sure that your models relations are defined

class Event < ActiveRecord::Base
  belongs_to :employee
  belongs_to :workorder
end

And then you can do something like: 

Event
  .where('starts_at > ?', '2018-03-14')
  .joins(:employee, :workorder)

or 

Event
  .joins(:employee, :workorder)
  .where('starts_at > ?', '2018-03-14')

And you don't need to worry which one goes first.

In general, it's suboptimal to create the SQL queries in rails if you don't absolutely need to because they're harder to maintain.

Upvotes: 1

Herv&#233; Piedvache
Herv&#233; Piedvache

Reputation: 798

You request should look at this :

select starts_at, ends_at, hours, employee.maxname, workorder.wonum 
  from events
 inner join employees on events.employee_id = employees.id
 inner join workorders on events.workorder_id = workorders.id
 where starts_at>'2018-03-14';

Upvotes: 0

Related Questions