Lets say I have two tables: users and organisations
users:
id int,
name varchar,
type int,
deleted bool
organisations:
id int,
name varchar
deleted bool
and I want to sort organisations by user of type 1 name. I know that when I want to sort by relation value I have to use join:
$organisationsModel -> join( 'users', 'organisations.id', '=', 'users.organisationId', 'left' )
-> select( 'organisations.*', 'users.name as userName' )
-> where( 'users.type', 1 )
-> where( 'users.deleted', 0 )
-> orderBy( 'userName', 'ASC );
but it only shows organisations that have user of type 1 (with deleted set to 0), and my question is: can I modify this query to also return values without proper user connected with it?
You need a left join with additional joining clauses in order to get all organizations, To order the results you can use conditional order by clause
$organisationsModel->leftJoin('users', function ($join) {
$join->on('organisations.id', '=', 'users.organisationId')
->where( 'users.type', 1)
->where( 'users.deleted', 0 );
})
->select( 'organisations.*', 'users.name as userName' )
->orderByRaw('CASE WHEN users.type = 1 AND users.deleted = 0 THEN 1 ELSE 0 END DESC')
->orderBy( 'userName', 'ASC );