1

to implement a basic relationship system I followed this model suggested from a website:

user_relationship:
------------------
- user_1
- user_2
- status
- user_action

Where user_1, user_2, user_action are foreign keys that references users.id and user_1 < user_2 to avoid duplicate tuples.

However I'm finding it difficult to write this SQL query in Eloquent (Laravel 5.5):

SELECT user_1, user_2
FROM user_relationships
WHERE (user_1 = :user OR user_2 = :user) AND status = 'friends'

This query should return all the tuples where user is either user_1 or user_2.

I have a very basic knowledge of Eloquent so I'm having a hard time making this work, but since I've seen many people saying that it's better to use Eloquent whenever possible I'd like to use it.

Sneppy
  • 379
  • 5
  • 20

1 Answers1

1

You do in wrong way. Better solution is to use relations in model. You can make relations such as:

  • One To One
  • One To Many
  • Many To Many
  • Has Many Through
  • Polymorphic Relations
  • Many To Many Polymorphic Relations

This will save your time, read it: https://laravel.com/docs/5.5/eloquent-relationships

Adam Kozlowski
  • 5,606
  • 2
  • 32
  • 51
  • In fact I asked how to write this SQL Query using Eloquent. I thought about using `hasMany()` on the `User` model but I don't know how to say that user can be either `user_1` or `user_2` and using `hasMany()` two times with two different foreign keys seems a bit stupid. – Sneppy Oct 27 '17 at 11:06
  • I would make two tables: 'users' and 'users_relations'. In 'users_relation' i would make four columns: 'id', 'first_user_id', 'second_user_id', 'relation_type'. I know that it is not direct answer, but in my opinion you need to change approach to eloquent. – Adam Kozlowski Oct 27 '17 at 11:10
  • I probably didn't explain myself, sorry. That is exactly what I have, two tables - `users` and `user_relationships` - and `user_relationships` has 4 main columns (apart from `id` and timestamps): `user_1`, `user_2`, `status` and `user_action` which is the user that caused last change in status. However I cannot achieve what I want with a single `hasMany("App\UserRelationship", "user_1")` because the user could also be in `user_2`. I could run two queries with `hasMany()` and `user_1`/`user_2` as foreign keys and then merge the results but I was looking for a better solution – Sneppy Oct 27 '17 at 11:21
  • 1
    In that case i would propose to use 'orWhere' in your query. Select all rows if user is in this or this column. https://laravel.com/docs/5.5/queries AND https://stackoverflow.com/questions/19325312/how-to-create-multiple-where-clause-query-using-laravel-eloquent – Adam Kozlowski Oct 27 '17 at 11:27