Is it possible to join two tables if table1.column value is in table2.column value-CodePudding

I have two tables i.e. users and actions_performed

users : id, name, etc

actions_performed : performed_by, action_id

Now the values in actions_performed can be as [ 'user:15', 'department:3', 'team:7' ] etc

Now i want to fetch the action performed by a specific user say id 1

i am implementing the same in Laravel 9,

can someone please help me either with the ORM relations or even simple SQL Queries

Users::where( function($q) use($searchString){
        $q->crossJoin('actions_performed', function($q) use($searchString){
            $q->where('performed_by','LIKE','%user%')
                ->where('value','LIKE',"%$searchString%");
        });
    })

CodePudding user response：

Thanks @all

I achieved the same by below

$query->when(!empty($searchString), function($q1) use($searchString){
          $q1->join('actions_performed', \DB::raw("CONCAT('user:' , `users`. `id`)"), '=', 'actions.performed_by')
              ->where(function ($q2) use($searchString){
                  $q2->where('actions.performed_by', 'LIKE', "%$searchString%");
              });
      });

CodePudding user response：

You can use the join() method to specify the relationship between the tables. you can join the tables using the following code

$data = DB::table('table1')
            ->join('table2', 'table1.id', '=', 'table2.table1_id')
            ->select('table1.*', 'table2.*')
            ->get();