CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
O.MeeKoh
O.MeeKoh

Reputation: 2156

SQL Query JOIN table through ActiveRecord relations

Using ActiveRecord relations I have a domain set up through which I am not able to figure out the right query to retrieve the desired information. This may not be the ideal model, but for my project I am required to use a join model that holds information about the relationship.

I want to filter the join table and only return the vehicles that have the status of dealership_vehicles.is_lot_ready = 'f', or 't'. Either way, the closest thing I came up with was a query 

SELECT *
FROM vehicles
INNER JOIN dealership_vehicles ON vehicles.dealership_vehicle_id = dealership_vehicles.id
WHERE dealership_vehicles.is_lot_ready = false;

At first I thought it worked, but upon investigating the data, it was bringing back way too many results, and when the query was changed to 'f' it did not bring any data back.

I cant figure out why, any suggestions would be appreciated.

class Dealership < ApplicationRecord
has_many :dealership_vehicles
has_many :vehicles, :through => :dealership_vehicles

class Vehicle < ApplicationRecord
belongs_to :dealership_vehicle
has_one :dealership, :through => :dealership_vehicle

class DealershipVehicle < ApplicationRecord
belongs_to :dealership
has_many :vehicles

And the following data in the tables:

vehicles
 id |  make  |  model  | year | dealership_vehicle_id |         created_at         |         updated_at
  1 | BMW    | M3      | 2016 |                     1 | 2018-01-20 16:59:13.196586 | 2018-01-20 16:59:13.196586
  2 | Toyota | Corolla | 2016 |                     2 | 2018-01-20 16:59:13.1998   | 2018-01-20 16:59:13.1998

dealerships
   id | name  |    city     |         created_at         |         updated_at
    1 | Ereve | Lith Harbor | 2018-01-20 16:59:13.067827 | 2018-01-20 16:59:13.067827
    2 | Rien  | Ellinia     | 2018-01-20 16:59:13.070359 | 2018-01-20 16:59:13.070359

dealership_vehicles
 id | dealership_id | is_lot_ready | buyer  | price |         created_at         |         updated_at         | permission
 28 |             4 | f            | Randy  | 30099 | 2018-01-20 16:59:13.183475 | 2018-01-20 16:59:13.183475 |
 27 |             3 | t            | Philip | 30099 | 2018-01-20 16:59:13.180412 | 2018-01-20 16:59:13.180412 |

Upvotes: 0

Views: 331

Answers (2)

pk-n
pk-n

Reputation: 576

Vehicle.joins(:dealership_vehicle).where(dealership_vehicles: {is_lot_ready: "f"})

Upvotes: 0

Brien Foss
Brien Foss

Reputation: 3367

This looks to me like you are just missing the single quotes around the 't' or 'f' as it appears you are working with literal strings.

For example:

SELECT *
FROM vehicles
INNER JOIN dealership_vehicles ON vehicles.dealership_vehicle_id = dealership_vehicles.id
WHERE dealership_vehicles.is_lot_ready = 'f';

Also, if your table only contains the data in your example, you will not be able to find any results because there are no correlating ID's.

See what I mean here, they do not match so you are not able to JOIN:

enter image description here

Upvotes: 1

Related Questions