Laravel join or left join queries AS different name

There are many situations where you need to join same table, but must have different names (you can define this as alias). Below is a simple code snippet to show how you can join same table with different name.

$limit = 10;
$page = 1;
$groups = DB::table('person')
       ->leftJoin('users as for_user', function ($join) {
           $join->on('for_user.id', '=', 'person.for_id')->where("for_user.id", "=", 10);
       })
       ->join('users as by_user', function ($join) {
           $join->on('by_user.id', '=', 'person.by_id');
       })
       ->where('person.status', '=', 1)
       ->select('person.id', 'person.first_name', 'by_user.first_name')
       ->limit($limit)->offset($page * $limit)
       ->get();

Hibernate/grails left join or inner join when using create criteria

Hibernate/grails left join or inner join or join when using create criteria. If you do not use

'CriteriaSpecification.LEFT_JOIN', then it would be a inner join. 

Here is the example as suppose 'User' belongsTo 'Role' which is nullable.

Now you want to left join 'Role' when creating criteria on 'User', then do 

the following. It would take all 'User' row where 'Role' is null or not.

But if you do not use 'CriteriaSpecification.LEFT_JOIN', then it would take only

'User.role' is not null. 

import org.hibernate.criterion.CriteriaSpecification;

List userList = User.createCriteria().list {
    eq("isActive", true);
    createAlias("role", "__role", CriteriaSpecification.LEFT_JOIN)
    eq("__role.someProperty", "some value");
}

Grails/GORM createCriteria.list to get specific/selected columns only | Alias To Entity Map ALIAS_TO_ENTITY_MAP | Grails Projections | Projections Data

Grails/GORM createCriteria.list to get specific/selected columns only | Alias To Entity Map ALIAS_TO_ENTITY_MAP | Grails Projections | Projections Data.

Below is a example of get some specific field instead of all fields for domain.

And you can left join another domain that is belongsTo or hasMany of parent domain.

You have to import below reference:
import org.hibernate.criterion.CriteriaSpecification

List list = DomainName.createCriteria().list {
    resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)
    "in"("id", [1L, 2L])
    createAlias('x', 'x_alias', CriteriaSpecification.LEFT_JOIN)
    projections {
        property("id", "id")
        property("x_alias.field", "field_reference");
    }
}

Output would be like below:

[
    [id:1, field_reference:value1],
    [id:2, field_reference:value2]
]