Query Champs

Many-to-Many Reciprocal Self Relationships

In this type of relationship, you have records that related to each other, and where saving or deleting the relationship in one direction should also maintain the relationship in the reverse direction.
The best example I can think of is in the area of genealogy.
Suppose you have a table with information about people, and you want to relate them (grandparent-parent-child etc). You always have to make the relation both ways, since it can’t be that person A is the child of person B, but B is not the parent of A. If you define this relation as reciprocal, when you save the relation between A and B, automatically the relation between B and A is saved as well. And when you delete the relationship, both relations are deleted.

class Person extends DataMapper {
    $has_many = array(
        'related_person' => array(
            'class' => 'person',
            'other_field' => 'person',
            'reciprocal' => TRUE
        ),
        'person' => array(
            'other_field' => 'related_person',
            'reciprocal' => TRUE
        )
    );
}

To get this to work, you will need:

• You need a relationship table to define this many-to-many relation, which is this case would be called 'persons_persons'.
• This table needs to contain a column called 'person_id' and a column called 'related_person_id'.

Note that this defined per relation. If you want this to work both ways, you need to specify the 'reciprocal' setting on BOTH relationship definitions.

To Convert Row data to columns:-
Suppose there are three tables users,fields,field_values
users is having data as below:

id	enabled	since
51	true	2
52	true	3
53	true	2
54	true	3
55	true	4

fields is having data as below:

id	name	type
1	name	text
2	country	select
3	email	text
4	gender	radio
5	religion	select

field_values is having data as below:

id	field_id	user_id	value
1	1	51	Prateek
2	2	51	India
3	3	51	abc@abc.com
4	4	51	Male
5	5	51	Hindu
6	1	52	Vijay
7	2	52	India
8	3	52	xyz@abc.com
9	4	52	Male
10	5	52	Hindu
11	1	53	Aashima
12	2	53	Denmark
13	3	53	abc@xyz.com
14	4	53	Female
15	5	53	Muslim
16	1	54	John
17	2	54	England
18	3	54	xyz@xyz.com
19	4	54	Male
20	5	54	Cristian
21	1	55	Yong
22	2	55	China
23	3	55	xyz@zxy.com
24	4	55	Male
25	5	55	Boddhism

Now to compose a query to find users who are here for more than 2 years:-

select id,since,s.name,s.country,s.email,s.gender,s.religion from users

inner join(

               select user_id,

                        max(if(field_id=1,value,0))as name,

                        max(if(field_id=2,value,0))as country,

                        max(if(field_id=3,value,0))as email,

                        max(if(field_id=4,value,0))as gender,

                        max(if(field_id=5,value,0))as religion, 

               from  field_values group by user_id

               ) s

on s.user_id=id and since>2

To find data from two tables:-
Suppose, table1 is factories(id, name), table2 is marblechallans(id, fromfactory_id, tofactory_id).

let the entries in table-2 are:
id   fromfactory_id   tofactory_id
1   1                        4
2   3                        2 
3   2                        1

let the entries in table-1 are: 
id   name
1   f1
2   f2
3   f3
4   f4

In order to find fromfactory-name and tofactory-name with challan no(id of table-2), the query will be:
select allfrom.id as challan,f.name as fromfactory,alto.`name` as tofactory from factories f
inner join
(select fromid as newid,id from marblechallans )allfrom on allfrom.newid=f.id
INNER JOIN
(select allto.id as aid,f1.name,allto.newid as altoid,f1.id as alltoid from factories f1
inner join
(select toid as newid,id from marblechallans)allto on allto.newid=f1.id)alto on alto.aid=allfrom.id

To find nth maximum from a database table Using PHP Script :-
     $braket="(";
     $cbracket=")";
     $query=" select max(id) from maxid";
     $whr=" where id<";
     $sql=" ";
     for($i=1;$i<=$n;$i++)
     {
         if($i!=$n)
            $sql.=$query.$whr.$braket;
         else
             $sql.=$query;
     }
     for($i=2;$i<=$n;$i++)
     {
         $sql.=$cbracket;
     } 


If else in mysql query :-
   the normal syntax goes like if( expression, true expression, false expression)

Suppose we have to update a field and set it's value to 1 if it is 0 or 0 if it is 1.
the field name is status and the table name is myTable, So query will go like this:-

update myTable set status=if(status= 0, 1, 0)


To find lowest unique value mysql query :-
 Now we need to select lowest value from a table and that should be unique as well. What I will do here is that I make a query to find occurrences of each value and now try to find the lowest among them having it's occurrence for one time only:

select min(value) from (select value,count(value) as cnt from myTable group by value) innerquery where innerquery.cnt=1