Grails Groovy Hibernate | Hibernate Criteria Builder | Projection | Custom Projection | Group By Projection | PropertyProjection

Grails Groovy Hibernate | Hibernate Criteria Builder | Projection | Custom Projection | Group By Projection | PropertyProjection.

It's easy to add projection custom. We can add custom projection and custom group by property.

import org.hibernate.criterion.Projections
import org.hibernate.criterion.Projection
import org.hibernate.criterion.PropertyProjection
import org.hibernate.Criteria
import org.hibernate.criterion.CriteriaQuery
import org.hibernate.HibernateException
org.hibernate.criterion.ProjectionList projectionList = []

ProjectionList projectionList = []
projectionList.add(new PropertyProjection("id") {
    @Override
    public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) throws HibernateException {
        return "this_.id as y0_"
    }
})
projectionList.add(new PropertyProjection("created") {
    @Override
    public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) throws HibernateException {
        return "MAX(this_.created) as y1_"
    }
})
projectionList.add(Projections.groupProperty("belongsTo.id"))
PropertyProjection groupBy = new PropertyProjection("id", true) {
    @Override
    public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) throws HibernateException {
        return "belongsto1_.id as y1_"
    }

    @Override
    public String toGroupSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return "belongsto1_.id"
    }
}
projectionList.add(groupBy)
Closure closure = {
    setProjection(projectionList)
}
List list =  Domain.createCriteria().list {
    and closure
}

But you want to make it simpler then you can use below function easily:

import org.hibernate.criterion.CriteriaSpecification
import org.hibernate.criterion.Projections
import org.hibernate.type.DoubleType
import org.hibernate.type.Type

resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)
projections {
    groupProperty("id", "id")
    groupProperty("name", "name")
    addProjectionToList(Projections.sqlProjection(
            "sum(id * 0.2) as totalPrice",
            ["totalPrice"] as String[],
            [DoubleType.INSTANCE] as Type[],
    ), "complexSqlCalculation")
}

Grails | Groovy | Create Criteria | Hibernate Criteria Builder | Custom Criteria Order | Custom Sort By | Custom Order Criteria

Grails | Groovy | Create Criteria | Hibernate Criteria Builder | Custom Criteria Order | Custom Sort By | Custom Order Criteria. 

In Grails we may need sometime to add sort / order by with some aggregate function as sum of two fields. Suppose we have a Grails / Groovy domain which has two field named "amount" and "tax", now we want to sort by sum of these two fields. So we can do that using below sample code: 

import groovy.lang.Closure
import org.hibernate.Criteria
import org.hibernate.HibernateException
import org.hibernate.criterion.CriteriaQuery
import org.hibernate.criterion.Order as CriterionOrder

Closure closure = {
    addOrder(new CriterionOrder("amount", params.dir) {
        @Override
        String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return "(this_.amount + this_.tax) asc"
        }
    })
    projections {
        property("id")
        property("amount")
    }
}
List list = Domain.createCriteria().list {
    and closure
}

Laravel 5 : Get Session Value | Another Session Value | Another Session Instance | Session Instance | HTTP Session | Session Mock | Mock Session | Duplicate Session | SessionManager | StartSession | Session Config | Get Session Config

Laravel 5 : Get Session Value | Another Session Value | Another Session Instance | Session Instance | HTTP Session | Session Mock | Mock Session | Duplicate Session | SessionManager | StartSession | Session Config | Get Session Config.

$sm = app("\\Illuminate\\Session\\SessionManager");
print_r($sm->getSessionConfig());

$sm = new \Illuminate\Session\SessionManager(app());
$ss = new \Illuminate\Session\Middleware\StartSession($sm);
$rq = \Illuminate\Http\Request::create("/", 'GET', array());
$ts = $ss->getSession($rq);
$ts->setId("7fe8a41c8185ef91e1c2b6aaab547ff34f2fed33");
$ts->start();

$ts->set("x", "value of x");

$ts->save();
print_r($ts->all());

And output below:

Array
(
    [driver] => file
    [lifetime] => 1440
    [expire_on_close] => 
    [encrypt] => 
    [files] => ...\project\storage\framework/sessions
    [connection] => 
    [table] => sessions
    [lottery] => Array
        (
            [0] => 2
            [1] => 100
        )

    [cookie] => laravel_session
    [path] => /
    [domain] => 
    [secure] => 
)
Array
(
    [_token] => Bm0Gu0lfy9JVI9TyQnddZVxBe3wndiiUR2NkDd9P
    [url] => Array
        (
        )

    [_previous] => Array
        (
            [url] => http://my_domain.com
        )

    [flash] => Array
        (
            [old] => Array
                (
                )

            [new] => Array
                (
                )

        )

    [login_web_59ba36addc2b2f9401580f014c7f58ea4e30989d] => 371
    [tz] => Asia/Dhaka
    [Hi] => Hello
)

Laravel 5 Session: Use Multiple Session Same Request | Duplicate Session | Use Value Of Another Session | Value From Another Session | PHP Raw Session

Laravel 5 Session: Use Multiple Session Same Request | Duplicate Session | Use Value Of Another Session | Value From Another Session | PHP Raw Session.

$session = new \Symfony\Component\HttpFoundation\Session\Session();
$session->setId("b858d2e0f84942ea2a3bb34bf2aa2a176f06b0c6");
$session->start();

$session->set("updated_time", date("Y-m-d H:i:s"));
$session->save();

echo session()->getId();
echo "<BR>";
print_r($session->all());

You can set Laravel default session save path using below example:

public function test(\Illuminate\Config\Repository $config)
{
    session_save_path($config->get("session.files"));
    $session = new \Symfony\Component\HttpFoundation\Session\Session();
    $session->setId("b858d2e0f84942ea2a3bb34bf2aa2a176f06b0c6");
    $session->start();

    $session->set("updated_time", date("Y-m-d H:i:s"));
    $session->save();

    echo session()->getId();
    echo "<BR>";
    print_r($session->all());
}

PHP Session: Use Multiple Session Same Request | Duplicate Session | Use Value Of Another Session | Value From Another Session

PHP Session: Use Multiple Session Same Request | Duplicate Session | Use Value Of Another Session | Value From Another Session.

<?php
$path = ini_get('session.save_path');
session_start();
$id = session_id();
$_SESSION["name"] = isset($_GET["name"]) ? $_GET["name"] : "No Name";
$_SESSION["tokens"] = array();
echo "<pre>";
echo "Session Save Path: " . $path . ",Session_id=$id\r\n\r\n";
willThisWork();
echo "\r\nORIGINAL_SESSION\r\n";
print_r($_SESSION);

function willThisWork() {
    $existing = session_id();
    session_write_close();

    ob_start();
    session_id("MyCommonSessionInstance");
    session_start();
    if (!isset($_SESSION["tokens"])) {
        $_SESSION["tokens"] = array();
    }
    for ($i = 0; $i < 10000; $i++) {
        array_push($_SESSION["tokens"], md5(time().rand(9999,999999)));
    }
    $start = microtime(true);
    if (count($_SESSION["tokens"]) > 100000) {
        $_SESSION["tokens"] = array_slice($_SESSION["tokens"], 75000);
    }
    $in_array = count($_SESSION["tokens"]).",EXISTS=" . (in_array($_SESSION["tokens"][count($_SESSION["tokens"]) - 1], $_SESSION["tokens"]));
    session_write_close();
    ob_get_clean();
    echo("TIME_TAKE=" . ((microtime(true) - $start) / 1000))." ms\r\n";


    session_id($existing);
    session_start();
    echo "COUNT=$in_array\r\n";
}
echo "</pre>";

And output is below:

Session Save Path: C:\xampp\tmp,Session_id=crolfji99hva2o0dflkg4ddj93

TIME_TAKE=1.2001037597656E-5 ms
COUNT=65000,EXISTS=1

ORIGINAL_SESSION
Array
(
    [name] => No Name
    [tokens] => Array
        (
        )

)

How can I set a default sort for tables in PHPMyAdmin (i.e. always “Primary key - Descending”) | MySQL - Automatic ordering by ID not working | MySQL sort by a column by default in phpMyAdmin

How can I set a default sort for tables in PHPMyAdmin (i.e. always “Primary key - Descending”) | MySQL - Automatic ordering by ID not working | MySQL sort by a column by default in PHPMyAdmin.

Even though its obnoxious in a lot of ways I use PHPMyAdmin all the time to debug database issues while writing PHP. By default it sorts tables by primary key ascending. 99% of the time I would rather have the newest data (my test data) shown at the top by default rather than the useless first few records ever saved.


Is there a way to configure PHPMyAdmin to show the newest records by default? To alter similar behavior?

In PHPMyAdmin 4.5.0, maybe in earlier versions too, you can set the $cfg['TablePrimaryKeyOrder'] config like so:

$cfg['TablePrimaryKeyOrder'] = 'DESC';

This defines the default sort order for the tables, having a primary key, when there is no sort order defines externally. Acceptable values : [‘NONE’, ‘ASC’, ‘DESC’]

This sets the default sort if the table has a primary key and that no other sort has been applied to it

Grails | Groovy | Hibernate | Query Builder | HibernateCriteriaBuilder | Criteria Builder | Criteria Builder To SQL | Grails Create Criteria To SQL Conversion | Grails Convert Query Builder To MySQL | Criteria Builder SQL Restriction | SQLRestriction | Grails SQL Restriction

Grails | Groovy | Hibernate | Query Builder | HibernateCriteriaBuilder | Criteria Builder | Criteria Builder To SQL | Grails Create Criteria To SQL Conversion | Grails Convert Query Builder To MySQL | Criteria Builder SQL Restriction | SQLRestriction | Grails SQL Restriction.

Below a query | criteria builder. We will convert below hibernate criteria builder to SQL our-self. So with this feature we can check each and every query generated by hibernate query builder and can take necessary step if we need any. With this feature we also can retrieve query binding parameters. At first we will see our grails domain structure I used in my example codes. Below example also to show how we can use SQL restriction in criteria builder. In SQL Restriction section we can write plain SQL.

class Subscription {
    Long id
    Account account
}

class Account {
    Long id
    User createdBy
}

class User {
    Long id
    String name
}

Now below is our criteria builder (as well as we called it closure will pass to criteria builder to execute it):

def test() {
    Closure closure = {
        HibernateCriteriaBuilder builder = delegate

        setMaxResults(2)
        String a1 = CriteriaToSql.createAlias(builder, "account")
        String a2 = CriteriaToSql.createAlias(builder, "createdBy", "${a1}.createdBy")

        println(builder.properties.sql_aliases)


        gt("id", -1L)
        ne("code", "NOT_EQ_CODE")
        gt("${a1}.id", -2L)
        gt("${a2}.id", -3L)

        sqlRestriction("exists (select a.id from account AS a where a.id=this_.id)")

        projections {
            property("code")
        }

        order("updated", "desc")
        order("id", "desc")
    }
    CriteriaToSql.check(Subscription.class, closure)
    render("")
}

And finally our desired class which will product SQL from given criteria builder:

import grails.orm.HibernateCriteriaBuilder
import org.apache.commons.lang.StringEscapeUtils
import org.hibernate.Criteria
import org.hibernate.engine.spi.LoadQueryInfluencers
import org.hibernate.engine.spi.SessionFactoryImplementor
import org.hibernate.internal.CriteriaImpl
import org.hibernate.internal.SessionImpl
import org.hibernate.loader.OuterJoinLoader
import org.hibernate.loader.OuterJoinableAssociation
import org.hibernate.loader.PropertyPath
import org.hibernate.loader.criteria.CriteriaJoinWalker
import org.hibernate.loader.criteria.CriteriaLoader
import org.hibernate.loader.criteria.CriteriaQueryTranslator
import org.hibernate.persister.entity.OuterJoinLoadable
import org.hibernate.sql.JoinType

import java.lang.reflect.Field
/**
 * Created by pritom on 17/07/2017.
 */
class CriteriaToSql {
    static int counter = 0

    static void check(Class domainClass, Closure closure) {
        Criteria criteria = domainClass.createCriteria().buildCriteria {
            and closure
        }

        CriteriaImpl c = (CriteriaImpl) criteria
        SessionImpl s = (SessionImpl) c.getSession()

        SessionFactoryImplementor factory = (SessionFactoryImplementor) s.getSessionFactory()
        String[] implementors = factory.getImplementors(c.getEntityOrClassName())
        LoadQueryInfluencers loadQueryInfluencers = new LoadQueryInfluencers()
        CriteriaLoader loader = new CriteriaLoader((OuterJoinLoadable) factory.getEntityPersister(implementors[0]), factory, c, implementors[0], loadQueryInfluencers)
        CriteriaQueryTranslator translator = new CriteriaQueryTranslator(factory, c, domainClass.canonicalName, "this_")
        CriteriaJoinWalker walker = new CriteriaJoinWalker(
                (OuterJoinLoadable) factory.getEntityPersister(implementors[0]),
                translator, factory, criteria, domainClass.canonicalName, loadQueryInfluencers
        )

        Field f = OuterJoinLoader.class.getDeclaredField("sql")
        f.setAccessible(true)
        String sql = (String) f.get(loader), printable = ""
        int index = 0, max = 250
        while (true) {
            if (sql.length() > (index * max) + max) {
                printable = printable + "\r\n" + sql.substring(index * max, max + (index * max))
                index++
            }
            else {
                printable = printable + "\r\n" + sql.substring(index * max)
                break
            }
        }
        println("SQL_QUERY=${printable}")

        f = CriteriaLoader.class.getDeclaredField("querySpaces")
        f.setAccessible(true)
        println("SQL_ALIASES=${walker.getAssociations()*.rhsAlias}")

        println("PARAMETERS=${translator.getQueryParameters().positionalParameterValues}")
    }

    static Map getSql(Class domainClass, Closure closure) {
        Criteria criteria = domainClass.createCriteria().buildCriteria {
            and closure
        }

        CriteriaImpl c = (CriteriaImpl) criteria
        SessionImpl s = (SessionImpl) c.getSession()

        SessionFactoryImplementor factory = (SessionFactoryImplementor) s.getSessionFactory()
        String[] implementors = factory.getImplementors(c.getEntityOrClassName())
        LoadQueryInfluencers loadQueryInfluencers = new LoadQueryInfluencers()
        CriteriaLoader loader = new CriteriaLoader((OuterJoinLoadable) factory.getEntityPersister(implementors[0]), factory, c, implementors[0], loadQueryInfluencers)
        CriteriaQueryTranslator translator = new CriteriaQueryTranslator(factory, c, domainClass.canonicalName, "this_")
        CriteriaJoinWalker walker = new CriteriaJoinWalker(
                (OuterJoinLoadable) factory.getEntityPersister(implementors[0]),
                translator, factory, criteria, domainClass.canonicalName, loadQueryInfluencers
        )

        Field f = OuterJoinLoader.class.getDeclaredField("sql")
        f.setAccessible(true)
        String sql = (String) f.get(loader)

        Map parts = [
                select: sql.substring(7, sql.indexOf("from")),
                from: sql.substring(sql.indexOf("from") + 5, sql.indexOf("where")),
                where: sql.substring(sql.indexOf("where") + 6),
                joins: [:]
        ]
        walker.getAssociations().each { OuterJoinableAssociation assoc ->
            String key = assoc.propertyPath.property
            PropertyPath propertyPath = assoc.propertyPath
            while (true) {
                if (propertyPath.parent?.property) {
                    key = propertyPath.parent.property + "." + key
                    propertyPath = propertyPath.parent
                }
                else {
                    break
                }
            }
            parts.joins[key] = [domain: assoc.propertyPath.property, alias: assoc.rhsAlias]
        }

        int lastIndex = -1
        translator.getQueryParameters().positionalParameterValues.each { param1 ->
            int index = parts.where.indexOf("?", lastIndex)
            if (index >= 0) {
                param1 = param1.rawValue()
                param1 = param1 instanceof String ?  "'${StringEscapeUtils.escapeSql(param1)}'" : param1
                parts.where = parts.where.substring(0, index) + param1 + parts.where.substring(index + 1)
                lastIndex = index + param1.toString().length()
            }
        }
        return parts
    }

    static String createAlias(HibernateCriteriaBuilder criteriaBuilder, String field, String aliasPath = field, JoinType joinType = JoinType.INNER_JOIN) {
        if (!criteriaBuilder.properties.lixt) {
            criteriaBuilder.metaClass.lixt = [:]
        }
        Criteria subCriteria = criteriaBuilder.criteria.subcriteriaList.find { it.path == aliasPath }

        if(subCriteria) {
            return subCriteria.alias
        }
        else {
            String alias = "${field}xxxxxxxx${++counter}"
            criteriaBuilder.createAlias(aliasPath, alias, joinType)
            /* The below variable is SQL alias path */
            String sqlAlias = alias.substring(0, 10) + (criteriaBuilder.properties.lixt.size() + 1) + "_"
            criteriaBuilder.properties.lixt[alias] = sqlAlias
            return alias
        }
    }
}

And finally we would have below output:

[PV4736-12429, MS2431-12503]

[PV4736-12429, MS2431-12503]

SQL_QUERY=
select this_.code as y0_ from subscription this_
inner join account accountxxx1_ on this_.account_id=accountxxx1_.id 
inner join user createdbyx2_ on accountxxx1_.created_by_id=createdbyx2_.id 
where (this_.id>? and this_.code<>? and accountxxx1_.id>? and createdbyx2_.id>?
and exists (select a.id from account AS a where a.id=this_.id))
order by this_.updated desc, this_.id desc

SQL_ALIASES=[accountxxx1_, createdbyx2_]

PARAMETERS=[-1, NOT_EQ_CODE, -2, -3]