CODEWITHSUNDEEP
phpsqllaravel
rose
rose

Reputation: 197

Problem with getting data from query in Laravel

I have two tables

field_values (with some data)

id  field_id  value  label          sort
1       1       1     Men            1
2       1       2     Women          2
3       2       3     Relationship   1
4       2       4     Chat           2
5       2       5     Friendship     3

user_interests (with some data)

user_id  field_id  value_id
   1        1         1
   1        2         4
   1        2         5

I am trying to write a query where I will get user with id 1 and have field_id 2 and to be able to echo in my blade value_id 4 and 5 but not to echo those ids but to echo value of 'label' column that corresponds to value_id form user_interests table in this case 4,5 thus Chat, Friendship from field_values table in this example. Here is what I tried but I get array of six elements which are Relationship, Chat, Friendship x2. Any help is appreciated.

query:

public static function queryFunction($userId)
{
    $results = DB::table('user_interests as uin')
        ->select(DB::raw("
            fv.*,
            uin.field_id, uin.value_id
        "))
        ->join('field_values as fv', 'fv.field_id', '=', 'uin.field_id')
        ->where('uin.field_id', 2)
        ->where('uin.user_id', $userId)
        ->get();

        dd($results);

    return $results;
}

Upvotes: 0

Views: 30

Answers (1)

Autista_z
Autista_z

Reputation: 2541

What about 2 clear steps, without join:

$user_interests = DB::table('user_interests')->select('value_id')->where('field_id', 2)->where('user_id', $userId)->get();

From this take values as array ($user_interests_values) and than

$results = DB::table('field_values')->whereIn('value', $user_interests_values)->get();

Upvotes: 1

Related Questions