MySQL "Group By" and "Order By"

A simple solution is to wrap the query into a subselect with the ORDER statement first and applying the GROUP BY later:

SELECT * FROM ( SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` ORDER BY `timestamp` DESC ) AS tmp_table GROUP BY LOWER(`fromEmail`)

This is similar to using the join but looks much nicer. Using non-aggregate columns in a SELECT with a GROUP BY clause is non-standard. MySQL will generally return the values of the first row it finds and discard the rest. Any ORDER BY clauses will only apply to the returned column value, not to the discarded ones. IMPORTANT UPDATE Selecting non-aggregate columns used to work in practice but should not be relied upon. Per the MySQL documentation "this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

Java - Always Name Your Thread Pools - Naming threads and thread-pools of ExecutorService - set name of thread

You could supply a ThreadFactory to newSingleThreadScheduledExecutor(ThreadFactory threadFactory). The factory will be responsibe for creating threads, and will be able to name them.

ExecutorService is a JDK API that makes asynchronous task execution easier. ExecutorService offers a pool of threads and an easy-to-use API for assigning tasks. The ExecutorService gives the name of the threads in the thread pool. This shot discusses how we can assign custom names to the threads of the thread pool of the ExecutorService.

BasicThreadFactory An ExecutorService employs a ThreadFactory to create its threads to execute tasks. In many circumstances, users do not need to worry about a ThreadFactory because the ExecutorService's default one will suffice. A custom ThreadFactory must be constructed with particular needs, such as thread naming.

import java.util.TimerTask; import java.util.concurrent.*; public class Main { public static void main(String[] args) { // instant thread execution ExecutorService executorService = Executors.newFixedThreadPool(3, namedThreadFactory("test-thread")); for (int i=0; i < 5; i++) { executorService.submit(() -> System.out.println(Thread.currentThread().getName())); } executorService.shutdown(); // scheduled thread execution ScheduledExecutorService executor = Executors.newSingleThreadScheduledExecutor(namedThreadFactory("scheduled-thread")); TimerTask repeatedTask = new TimerTask() { @Override public void run() { try { System.out.println(Thread.currentThread().getName()); } finally { executor.shutdownNow(); } } }; executor.schedule(repeatedTask, 1L, TimeUnit.MINUTES); } static ThreadFactory namedThreadFactory(String name) { return new YourThreadFactory(name); } } class YourThreadFactory implements ThreadFactory { private String name = "[No Name]"; YourThreadFactory(String name) { this.name = name; } public Thread newThread(Runnable r) { return new Thread(r, name); } }

How to convert result table to JSON Array or JSON Object or JSON String in MySQL

I'd like to convert result table to JSON Array of Object in MySQL using preferably only plain MySQL commands. For example with query SELECT name, phone FROM person; | name | phone | | Jack | 12345 | | John | 23455 | the expected JSON output would be as below as per my requirement: [ { "name": "Jack", "phone": 12345 }, { "name": "John", "phone": 23455 } ]

The maximum value for group_concat_max_len is 18446744073709551615. To set the variable forever use SET GLOBAL group_concat_max_len=4294967295;

Example 1: SELECT i.id, JSON_OBJECT('id', ig.id, "name", ig.name) AS 'group_json' FROM item i LEFT JOIN item_group ig ON i.item_group_id=ig.id ORDER BY i.id DESC LIMIT 10;

Example 2 (Group Concat): SELECT ig.item_sales_rate_id, GROUP_CONCAT(DISTICT JSON_OBJECT('id', ig.id, "status", ig.status)) AS 'group_json' FROM item_sales_rate i LEFT JOIN item_sales_uom ig ON i.id=ig.item_sales_rate_id WHERE ig.item_sales_rate_id IS NOT NULL GROUP BY ig.item_sales_rate_id ORDER BY i.id ASC LIMIT 10;

MySQL casting from decimal to string - mysql cast to varchar - mysql convert decimal to string char varchar

MySQL - casting from decimal to string select CAST(ROUND(345345345345345345345353453453453454.4345345,6) AS CHAR(65)) AS 'big_number'; don't try to cast to varchar, there is a bug at MySQL end

Grails on Groovy - join tables using createAlias with additional condition along with sql on condition for additional filter of data

We frequently use createAlias to create alias between entity. It actually create connection based on foreign key and primary key between two entities. Below is a example how we create alias between entities:

Table1.createCriteria().list { createAlias("scores", "scores") }

Which will produce SQL like below where we can see that foreign key of table1 create a link with table2_child based on primary key id as following:

select ... from table1 this_ inner join table2_child scores1_ on (this_.id=scores1_.table1_id)

But if we need more on add to filter when joining them, yeah, we can do that. We can add extra conditions to on part of that SQL. To do so, first need to create a AliasBuiilder.groovy file like below:

package com.gradle_app import org.hibernate.criterion.Criterion import org.hibernate.sql.JoinType abstract class AliasBuilder { static final Object synk0 = new Object() static Long number = 0L static void fixCriteriaBuilderProps(criteriaBuilder) { if (!criteriaBuilder.properties.containsKey("aliases")) { criteriaBuilder.metaClass.aliases = [:] criteriaBuilder.metaClass.data = [:] } criteriaBuilder = null } static String createAlias0(criteriaBuilder, String field, String aliasPath = field, JoinType joinType = JoinType.INNER_JOIN, Criterion customCriterion = null) { aliasPath = aliasPath ? aliasPath : field joinType = joinType ? joinType : JoinType.INNER_JOIN fixCriteriaBuilderProps(criteriaBuilder) def subCriteria = criteriaBuilder.criteria.subcriteriaList.find { it.path == aliasPath } if(subCriteria) { try { return subCriteria.alias } finally { criteriaBuilder = field = aliasPath = joinType = subCriteria = null } } else { String alias = "${field}_custom_name_${nextSerialNumber()}".toLowerCase() criteriaBuilder.createAlias(aliasPath, alias, joinType, customCriterion) String other = alias.substring(0, criteriaBuilder.sessionFactory.dialect.getMaxAliasLength()) + (criteriaBuilder.properties.aliases.size() + 1) + "_" criteriaBuilder.properties.aliases[alias] = other criteriaBuilder.properties.data[aliasPath] = alias try { return alias } finally { criteriaBuilder = field = aliasPath = joinType = subCriteria = alias = other = null } } } static String nextSerialNumber() { Long serial = null try { synchronized (synk0) { number = serial = number + 1L if (number == Long.MAX_VALUE) { number = 1L } } return serial.toString() } finally { serial } } }

And another CustomCriteria.groovy file like this: package com.gradle_app import org.hibernate.Criteria import org.hibernate.HibernateException import org.hibernate.criterion.CriteriaQuery import org.hibernate.criterion.Criterion import org.hibernate.engine.spi.TypedValue import org.hibernate.internal.util.StringHelper class CustomCriteria implements Criterion { private static final TypedValue[] NO_VALUES = new TypedValue[0] public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException { String alias = criteriaQuery.getSQLAlias(criteria) return "${alias}.status='1'" } public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException { return NO_VALUES } }

And you can use the above custom condition using as below example: void checkCustomCriteria() { List list = Table1.createCriteria().list { //setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP) String scores = AliasBuilder.createAlias0(delegate, "scores", "scores", JoinType.LEFT_OUTER_JOIN, new CustomCriteria()) "projections" { "groupProperty" "id", "id" addProjectionToList(Projections.sqlProjection( "GROUP_CONCAT(${delegate.aliases[scores]}.id) as scores", ["scores"] as String[], [StringType.INSTANCE] as Type[] ), "scores") } } list.each { dataRow-> println("Data row=${dataRow}") } }

Which will generate below SQL with additional condition on statement ( scores1_.status='1' ) select ... from table1 this_ inner join table2_child scores1_ on this_.id=scores1_.table1_id and ( scores1_.score.status='1' ) where this_.id<>?

Laravel Blade @include .html files | Laravel Blade Highlight Change Tags when use AngularJS along with Laravel

I am going to use AngularJS along with Laravel, and I wanted to change Laravel tags to [[ ]], it can achived by below codes: Blade::setContentTags('[[', ']]'); // for variables and all things Blade Blade::setEscapedContentTags('[[[', ']]]'); // for escaped data

But my solution is to have Angular and Blade works very simple, I create a another xxx.php every time instead of xxx.blade.php. I need some Angular and name this partial just '.php' and not '.blade.php'. Lets say I have a user create form named "create.blade.php" inside "views/user" directory using below content: @extends('layouts.user') @section('content') <form> <h2>Create User</h2> @include('user.createBody'); </form> @endsection Now I will create an php file named "createBody.php" inside "views/user" directory and put some html there: <div class="row"> <h1>User Name={{userName}}</h1> <div class="col-lg-4 col-sm-6"> <label>Name</label> <input type="text" class="form-control" ng-model="userName"> </div> <div class="col-lg-4 col-sm-6"> <label>ID</label> <input type="text" class="form-control"> </div> </div> So here we can use {{ }} tags both in Laravel and AngularJS