/ Gists

Gists

On gists

first/least/max row per group in SQL

MySql MySql tricks

baron-schwartz.sql #

+--------+------------+-------+
| type   | variety    | price |
+--------+------------+-------+
| apple  | gala       |  2.79 | 
| apple  | fuji       |  0.24 | 
| apple  | limbertwig |  2.87 | 
| orange | valencia   |  3.59 | 
| orange | navel      |  9.36 | 
| pear   | bradford   |  6.05 | 
| pear   | bartlett   |  2.14 | 
| cherry | bing       |  2.55 | 
| cherry | chelan     |  6.33 | 
+--------+------------+-------+


#1 self join
select f.type, f.variety, f.price
from (
   select type, min(price) as minprice
   from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;


#2 correlated subquery, less efficient
select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type);


+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | fuji     |  0.24 | 
| orange | valencia |  3.59 | 
| pear   | bartlett |  2.14 | 
| cherry | bing     |  2.55 | 
+--------+----------+-------+


###################################
# $n from each
###################################

#1  self join?
select type, variety, price
from fruits
where (
   select count(*) from fruits as f
   where f.type = fruits.type and f.price <= fruits.price
) <= 2;



#2 union
(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)
union all
(select * from fruits where type = 'cherry' order by price limit 2)


#3a variables 
set @num := 0, @type := '';

select type, variety, price
from (
   select type, variety, price,
      @num := if(@type = type, @num + 1, 1) as row_number,
      @type := type as dummy
  from fruits
  order by type, price
) as x where x.row_number <= 2;


#3b variables with force without parent subquery
set @num := 0, @type := '';

select type, variety, price,
      @num := if(@type = type, @num + 1, 1) as row_number,
      @type := type as dummy
from fruits force index(type)
group by type, price, variety
having row_number <= 2;
On gists

Ranking variables (2 ways)

MySql MySql tricks

ranking.sql #

#1 way
set @radek=0; 
select @radek:=@radek+1 rownum, t.* FROM tabulka t ORDER BY t.body DESC;


#2 way
select @radek:=@radek+1 rownum, t.* from (SELECT @radek:=0) r, tabulka t



SET @poradi:=0,@poradib:=0;
SELECT @poradi:=@poradi+1, @poradi as poradi, IF(id=201, @poradib:=@poradi, 0), id, name FROM table ORDER BY `name` DESC;
SELECT @poradib;
On gists

Ranking via variables

MySql MySql tricks

ranking.sql #

set @type := '', @num := 1;

select type, variety, price, row_number
from (
   select type, variety, price,
       @num := if(@type = type, @num + 1, 1) as row_number,
       @type := type as dummy
   from fruits
) as x
where row_number <= 2;

+--------+----------+-------+------------+
| type   | variety  | price | row_number |
+--------+----------+-------+------------+
| apple  | gala     |  2.79 |          1 | 
| apple  | fuji     |  0.24 |          2 | 
| orange | valencia |  3.59 |          1 | 
| orange | navel    |  9.36 |          2 | 
| pear   | bradford |  6.05 |          1 | 
| pear   | bartlett |  2.14 |          2 | 
| cherry | bing     |  2.55 |          1 | 
| cherry | chelan   |  6.33 |          2 | 
+--------+----------+-------+------------+
On gists

Private members and prototypes

JavaScript-OOP JavaScript

example.js #

function Dog(name, color) {
    //private members
    var name = name,
        color = color;

    //public method
    this.getColor = function () {
        return color;
    }
    //public method
    this.getName = function () {
        return name;
    }
}
Dog.prototype = (function () {
    //private member
    var breed = 'French Bulldog',
    
    //private method
    getBreed = function () {
        return breed;
    };
    
    //public interface
    return {
        getBreed: function () {
            return breed;
        },
        getMessage: function () {
            return this.getColor() + ' dog, ' + 
                getBreed() + ' breed, named ' + 
                this.getName() + ' is missing';
        }
    }
})();
On gists

Maximum value by each row

•
Popular ⭐ MySql MySql - advanced Nette-Tricks

structure.sql #


CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
    
    

    
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
On gists

PHP array sorting

PHP

ways.php #

  <?php
  $array = [
              [
                ['name'=>'John B'],
                ['age'=>30],
                ['sizes'=>
                          [
                          'weight'=>80, 
                          'height'=>120
                          ]
                ]
              ],
              [
                ['name'=>'Marie B'],
                ['age'=>31],
                ['sizes'=>
                          [
                          'weight'=>60, 
                          'height'=>110
                          ]
                ]
              ],
              [
                ['name'=>'Carl M'],
                ['age'=>12],
                ['sizes'=>
                          [
                          'weight'=>70, 
                          'height'=>100
                          ]
                ]
              ],
              [
                ['name'=>'Mike N'],
                ['age'=>19],
                ['sizes'=>
                          [
                          'weight'=>70, 
                          'height'=>150
                          ]
                ]
              ],
              [
                ['name'=>'Nancy N'],
                ['age'=>15],
                ['sizes'=>
                          [
                          'weight'=>60, 
                          'height'=>150
                          ]
                ]
              ],
              [
                ['name'=>'Cory X'],
                ['age'=>15],
                ['sizes'=>
                          [
                          'weight'=>44, 
                          'height'=>150
                          ]
                ]
              ]
  ];

  

   //Method1: sorting the array using the usort function and a "callback that you define"
   function method1($a,$b) 
   {
     return ($a[2]["sizes"]["weight"] <= $b[2]["sizes"]["weight"]) ? -1 : 1;
   }
   usort($array, "method1");
   print_r($array);




//Method 2: The bubble method
$j=0;
$flag = true;
$temp=0;

while ( $flag )
{
  $flag = false;
  for( $j=0;  $j < count($array)-1; $j++)
  {
    if ( $array[$j][2]["sizes"]["weight"] > $array[$j+1][2]["sizes"]["weight"] )
    {
      $temp = $array[$j];
      //swap the two between each other
      $array[$j] = $array[$j+1];
      $array[$j+1]=$temp;
      $flag = true; //show that a swap occurred
    }
  }
}
print_r($array);



  //Method3: DIY 
  $temp = [];
  foreach ($array as $key => $value)
    $temp[$value[2]["sizes"]["weight"] . "oldkey" . $key] = $value; //concatenate something unique to make sure two equal weights don't overwrite each other
  ksort($temp); // or ksort($temp, SORT_NATURAL); see paragraph above to understand why
  $array = array_values($temp);
  unset($temp);
  print_r($array);



  // Method 4
  array_multisort(array_map(function($element) {
    return $element[2]['sizes']['weight'];
}, $array), SORT_ASC, $array);


print_r($array);
On gists

Prev / Next row - mysql select - variables, corelated subquery

•
MySql MySql tricks

scheme.sql #

create table example(
  id int not null primary key,
  value varchar(50)
);

insert into example
values (0,100),(2,150),(3,200),(6,250),(7,300)
On gists

Remove diacritics - strtr

PHP

rem-dia.php #

<?php

    private function removeDiacritics($str)
    {
        return strtr($str, "ÁÄČÇĎÉĚËÍŇÓÖŘŠŤÚŮÜÝŽáäčçďéěëíňóöřšťúůüýž", "AACCDEEEINOORSTUUUYZaaccdeeeinoorstuuuyz");
    }
On gists

Procedury - MIX

•
MySql MySql - advanced

poradi-tymu.sql #

CREATE PROCEDURE SP_poradi()
BEGIN
 
DECLARE s_poradi INT(5);
DECLARE s_body INT(5);
DECLARE temp_body INT(5) DEFAULT 0;
DECLARE s_tym VARCHAR(50);
DECLARE my_rank INT(5) DEFAULT 0;
DECLARE done TINYINT(1) DEFAULT 0;
 
DECLARE i INT;
 
DECLARE rank CURSOR FOR
SELECT tym, body, poradi FROM poradi
ORDER BY body DESC;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 
SET i = 0;
OPEN rank;
rank_loop:  LOOP
 
FETCH rank INTO s_tym, s_body, s_poradi;
IF done THEN LEAVE rank_loop; END IF;

 
IF (temp_body <> s_body)
  THEN UPDATE poradi SET poradi = i + 1 WHERE tym = s_tym;
ELSE
  UPDATE poradi SET poradi = i + 0 WHERE tym = s_tym;
END IF;
 
SET i=i+1;
SET temp_body = s_body;  
 
 
END LOOP rank_loop;
CLOSE rank;
 
END;
On gists

Nette own storage via Session - wrapper

Nette PHP

Storage.php #

<?php

namespace Model;

use Nette;

class DemandStorage extends Nette\Object
{
    protected $session;
    public function __construct(Nette\Http\Session $session)
    {
        $this->session = $session->getSection('demandForm');    
    }

    public function flush()
    {
        $this->session->remove();
    }

    public function getRawData()
    {
        return isset($this->session['formData']) ? $this->session['formData'] : array();
    }

    public function getStepNumber()
    {
        return isset($this->session['step']) ? $this->session['step'] : 1;
    }

    public function setRawData($data)
    {
        $this->session['formData'] = $data;
        return $this;
    }

    public function addRawData($data)
    {
        $originalData = $this->getRawData();
        $data = array_merge($originalData, $data);
        $this->setRawData($data);
        return $this;
    }

    public function setStepNumber($step = 1)
    {
        $this->session['step'] = $step;
        return $this;
    }

    public function getValue($name, $default = NULL)
    {
        $data = $this->getRawData();
        return Nette\Utils\Arrays::get($data, $name, $default);
    }

    public function setValue($name, $value)
    {
        $data = $this->getRawData();
        $data[$name] = $value;
        $this->setRawData($data);
        return $this;
    }

    public function unsetValue($name)
    {
        $data = $this->getRawData();
        if(isset($data[$name]))
            unset($data[$name]);
        $this->setRawData($data);
        return $this;
    }

    public function issetValue($name)
    {
        $data = $this->getRawData();
            return isset($data[$name]);
    }

}