CODEWITHSUNDEEP
mysqlruby-on-rails
Michael
Michael

Reputation: 1042

Help searching MYSQL database with a JOIN - possible self JOIN?

In a View i have a table of users combining data from multiple database tables. The important tables are Users and Invitations. Im performing a join

LEFT JOIN invitations ON (users.id = invitations.invitee_id)

which gives me access to users.invitee_id, but i also have dynamic search parameters in my query and i cant search for an inviter by email because the inviter email is determined by using the invitee_id joined with the users table on user id.

How can search for the inviter by email?

heres the entire query

    ["SELECT users.id, users.login, users.email, invitations.inviter_id, FROM users 
      LEFT JOIN invitations ON (users.id = invitations.invitee_id)
      WHERE (users.email LIKE ? OR users.id LIKE ? OR users.login LIKE ?)",
      "%#{params[:search]}%","%#{params[:search]}%","%#{params[:search]}%"]

schema

USERS: id, login, email

INVITATIONS: id, inviter_id, invitee_id

Upvotes: 0

Views: 115

Answers (1)

a1ex07
a1ex07

Reputation: 37364

Join Users one more time 

 .... LEFT JOIN users u2 ON (invitations.inviter_id = u2.user_id) 
 WHERE ... AND u2.user_name =[your condition]

Upvotes: 1

Related Questions