MYSQL: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Hey guys,

Recently when we run one of our query with group by . we encountered an MYSQL error as like below:

ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column <column name> which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

When we dig in deeper, we came to know that we have enabled only_full_group_by  which is by default in MySQL 5.7.5 and later. 

For example. Query

select name , phone_number, count(*)  from contact 

group by phone_number ;

contact table :

----------------------

  name     phone_number

   Raj       123

   Ram     123 

   Karan    456

See above table has 2 names pointing to same number 123 . so when we group by the data with phone_number it tries to count no . of records based on phone_number.

Now when we keep the name in the selection list . MYSQL has to pick only one name to show to user . 

System will confuse which name to pick Raj / Ram . 

so , when we enable only_full_group_by . we are letting mysql to not accept queries with functional independence in the selection list.  if they are functionally dependent or primary key then it will work. 

To overcome above problem :  2 solutions

Solution 1: 

   Disable only_full_group_by . follow below link 

     https://stackoverflow.com/questions/23921117/disable-only-full-group-by

Solution 2:

    ANY_VALUE()  function for non-agggregated columns in the selection list. By using this, system picks any one name out of multiple values. 

     select any_value(name) , phone_number, count(*)  from contact 

group by phone_number ; 

Please don't keep the non-aggregated columns in selection list at any cost. If you think its necessary then follow above steps to overcome your problem 

More on only_full_group_by

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Angular service Re-initialised / Angular service instantiated many times

 Hey Guys,

Recently i came cross a problem with Angular 2 + service  constructor got called multiple times. 

Generally , Angular services are singleton meaning it will be called / instantiated only once across the app if the scope of injection is root. 

If we want to re-create / call constructor mutliple times then we have to keep the service in the specific module PROVIDERS. When we specify in providers then the service will be re-created for that particular module you specified. 

The issue i faced:

 > we have the service with  root injectable.  it clearly states that the service available across the root. 

@Injectable({
  providedIn: 'root'
})

export class TestService

But we kept the service in Providers for particular module say  TestModule

providers: [
    TestService
  ]
This causing the service to be called many times and all the data inside service is re-freshed. 

so, when u keep the service at injectable root then dont put it in providers until its needed or necessary. 

Angular Reactive Form Validation with Custom Validators

Hey Guys, 

I worked on Angular Form group and Form controls validation recently.  we can validate the whole form controls at one go with angular cross field validation technique. 

Fundamentals & Reference :

Angular Reactive Forms:
https://angular.io/guide/reactive-forms

Validation  with custom validators for whole form:

https://angular.io/guide/form-validation#cross-field-validation


All we have to do is supply the certain options to new FormGroup() constructor. 

So, today we talk about two such properties of FormGroup 

1) Validators:

   This property is used to pass custom validator function. we create the validation functions as a directive which implements   ValidatorFn interface  of angular . It takes an Angular control object as an argument and returns either null if the form is valid, or ValidationErrors otherwise.

Note: Data cloned from the official angular documentaion . 
    Example : 

Form Group : 

const heroForm = new FormGroup({ 'name': new FormControl(), 'alterEgo': new FormControl(), 'power': new FormControl() });

To add a validator function . give option like below:

const heroForm = new FormGroup({ 'name': new FormControl(), 'alterEgo': new FormControl(), 'power': new FormControl() }, { validators: identityRevealedValidator });
Here identityRevealedValidator is a cross field validator function writte in a separate directive like below

Directive:

export const identityRevealedValidator: ValidatorFn = (control: FormGroup): ValidationErrors | null => { const name = control.get('name'); const alterEgo = control.get('alterEgo'); return name && alterEgo && name.value === alterEgo.value ? { 'identityRevealed': true } : null;

};Template goes like this to show errors:

<div *ngIf="heroForm.errors?.identityRevealed && (heroForm.touched || heroForm.dirty)" class="cross-validation-error-message alert alert-danger">
Name cannot match alter ego. </div>

Now Lets talk about another property of FormGroup
2) updateOn
 > Reports the update strategy of the AbstractControl (meaning the event on which the control updates itself). Possible values: 'change' | 'blur' | 'submit' Default value: 'change'
> Can be used for formControl / formGroup
submit:
> updates the formGroup values on submit instead each blur / change event
blur:
> on blur , the values get updated for formGroup / formControl based on given context.

change:
> On control change the values get updated.


In our above example, if we want to update formGroup values on submit then
const heroForm = new FormGroup({
'name': new FormControl(), 'alterEgo': new FormControl(), 'power': new FormControl() }, { validators: identityRevealedValidator , updateOn: 'submit' });

// the above will set updateOn:'submit/blur/change' for all controls as they are grouped under one formGroup.