Question 300099 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Rails Multiple join query

I have the below Model structure

class Transaction
  belongs_to :company
  belongs_to :user
end

class Company
  belongs_to :address
end

class User
  belongs_to :address
end

classAddress
 has_many :companies
 has_many :users
end

I want to query all transactions and get both company address and user address

 Transaction.joins([company: :address], [user: :address])
    .select('company.addresses.line_1 as company_add_line1', 
       'user.addresses.line_1 as user_add_line1')

For the syntax ‘company.addresses.line_1’, I am getting the below error

ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'company.addresses.line_1' in 'field list'

How t achieve this?

Tags:

2

Answers


  1. 0

    I note that your User and Company each should have_many :transactions

    Your syntax fails b/c you have ActiveRecord-like syntax (e.g. companies.addresses.line1 inside a SQL snippet)

    You need something like this…

    select_user_add = <<-SEL
  (select line1 
   from addresses
   joins users
     on users.address_id = addresses.id
   where transaction.user_id = users.id ) as user_add_line1
  SEL

select_company_add = <<-SEL
  (select line1 
   from addresses
   joins companies
     on companies.address_id = addresses.id
   where transaction.user_id = companies.id ) as company_add_line1
  SEL

Transaction.select("transactions.*, #{select_user_add}, #{select_company_add")
    Login or Signup to reply.
  2. 0

    You have not mentioned has_many :transactions in the company and users model.

    Cross-check the field with your schema fields this will work.

    transaction_id = 1  # actual transaction ID

transaction_data = Transaction.joins(company: :address, user: :address)
                           .where(transactions: { id: transaction_id })
                           .select('transactions.*, companies.*, users.*',
                                   'company_addresses.line_1 as company_add_line1',
                                   'user_addresses.line_1 as user_add_line1')
                           .first
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search