/ Gists

Gists

On gists

Swap two columns values

MySql MySql tricks MySql - advanced

swap.sql #

UPDATE swap_test s1, swap_test s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;

-- tipliga
UPDATE
 _tipliga_tipy_hracu t1, 
 _tipliga_tipy_hracu t2  
SET t1.g1=t1.g2, t1.g2=t2.g1 WHERE t1.id=t2.id
AND (t1.id_zapasu = 1462)
On gists

Document, element, outside click, propagation

JavaScript jQuery

example.js #

// 1
$(document).click(function(e){

   if($(e.target).closest('#dropdownID').length != 0) return false;
   $('#dropdownID').hide();
});


// 2
$('body').click(function(e) {
    if ($(e.target).closest('.notification-container').length === 0) {
        // close/animate your div
    }
});


//3, https://www.tutorialrepublic.com/faq/hide-dropdown-menu-on-click-outside-of-the-element-in-jquery.php
$(document).ready(function(){
        // Show hide popover
        $(".dropdown").click(function(){
            $(this).find(".dropdown-menu").slideToggle("fast");
        });
    });
    $(document).on("click", function(event){
        var $trigger = $(".dropdown");
        if($trigger !== event.target && !$trigger.has(event.target).length){
            $(".dropdown-menu").slideUp("fast");
        }            
    });

// 4, http://benalman.com/projects/jquery-outside-events-plugin/
$(document).ready(function(){
    $(".notification-button").click(function(){
        $('.notification-container').toggle().animate({"margin-top":"0px"}, 75); 
    }); 

    $('.notification-wrapper').bind('clickoutside', function (event) {
        $('.notification-container').animate({"margin-top":"-15px"}, 75, function(){$(this).fadeOut(75)});
    });
});
On gists

Visual Studio Code Settings Sync Gist

Nette-Forms

cloudSettings #

{"lastUpload":"2019-10-25T08:28:32.767Z","extensionVersion":"v3.4.3"}
On gists

Novato - ranks

MySql tricks MySql - advanced AW

rank-model.php #


    public function triggerEvent(array $events = [], Nette\Database\Table\ActiveRow $section = null, $debug = false)
    {
        if($debug)
        {
            $logFile = INDEX_DIR . '/trigger-event.log';
            $logData = [];
            foreach ($events as $event)
            {
                $logData[] = sprintf('%s | %s | sectionId: %d', 
                                        date('Y-m-d H:i:s'), 
                                        $event, 
                                        ($section !== null) ? $section->id : $section
                                    ) 
                                    . PHP_EOL;
            }
            
            file_put_contents('nette.safe://' . $logFile, $logData, FILE_APPEND);
        }

        $sectionIndependentEvents = ['onAny'];

        $whereOrParams = $whereOr = [];
        foreach($events as $event)
        {
            $whereOr[] = 'FIND_IN_SET(?, event)';
            $whereOrParams[] = $event;
        }

        $presenters = $this->connection->table('catalogue_presenter_config')
            ->where(implode(' OR ', $whereOr), ...$whereOrParams);
        
        $pagesToInvalidate = [];                                       
        foreach ($presenters as $presenterRow)
        {
            $pages = $this->connection
                ->table('catalogue_page')
                ->where('catalogue_section.catalogue_id', $section->catalogue_id)
                ->where('presenter_id', $presenterRow->presenter_id);

            $invalidateIndependent = FALSE;
            foreach(explode(',', $presenterRow->event) as $event)
                if(in_array($event, $sectionIndependentEvents))
                    $invalidateIndependent = TRUE;

            if(!$invalidateIndependent)
                $page->where('catalogue_section_id', $section->id);
            
            foreach ($pages as $page)
                $pagesToInvalidate[$page->id] = $page;
        }   
        
        foreach ($pagesToInvalidate as $pageId => $page)
            $this->invalidPdf($page);
    }

    public function recountPageNumbersInCatalogue($catalogueId)
    {
        $this->connection->query('
            UPDATE 
            catalogue_page 
            JOIN (
                SELECT
                tmp2.*,
                @totalRank := @totalRank + tmp2.prev as lastPageNumberInCatalogue
                FROM 
                (SELECT @totalRank := 0) as tempTotalRank,
                (
                    SELECT
                    tmp.*,
                    @prev as prev,
                    @prev := tmp.pdf_pages_count
                    
                    FROM
                    (
                        SELECT 
                        @prev := 0,
                        t2.name AS sectionName,
                        t2.rank AS sectionRank,
                        t1.rank AS pageRank,
                        t1.id,
                        t1.pdf_pages_count
                        
                        FROM 
                    
                        catalogue_page t1
                        JOIN catalogue_section t2 ON t2.id = t1.catalogue_section_id
                        WHERE t2.catalogue_id = ?
                        ORDER BY sectionRank ASC, pageRank ASC
                    ) AS tmp
                    
                ) AS tmp2

            ) tmpJoin ON tmpJoin.id = catalogue_page.id
            SET page_number = lastPageNumberInCatalogue + 1
        ', $catalogueId);
    }
On gists

Tipliga - SQL - multiplevariables - ranks

MySql

tipliga.sql #

	INSERT INTO
		_tipliga_poradi
		(
			poradi,
			id_hrace,
			liga,
			rocnik,
			pz,
			v,
			r,
			p,
			nz,
			score,
			pts
		)
		
		SELECT
		rank,
		hrac,
		liga,
		rocnik,
		pz,
		v,
		r,
		p, 
		NETIPOVANO,
		SKORE,
		PTS
		FROM
		
		(
		
			SELECT
			*,
			IF(SKORE = @lastScore AND @lastPts = PTS, @currentRank := @currentRank, @currentRank := @i) as rank,
			@i := @i + 1,
			@lastScore := SKORE,
			@lastPts := PTS
			FROM
			(
			
			
			SELECT
							hrac, 
							h.prezdivka,
							SUM(PZ) as PZ,
							SUM(V) as V,
							SUM(R) as R,
							SUM(P) as P,
							SUM(NETIPOVANO) as NETIPOVANO,
							SUM(PTS) as SKORE,
							SUM(V)*3 + SUM(R)*1 + SUM(NETIPOVANO)*-1 as PTS,
							liga, 
							rocnik
			
							FROM
							(
										SELECT
										domaci as hrac,
										COUNT(*) as PZ,
										SUM(IF (domaci_body>host_body,1,0)) as V,
										SUM(IF (domaci_body=host_body AND domaci_tipoval = 1 AND host_tipoval = 1, 1,0)) as R,
										SUM(IF (domaci_body<host_body AND domaci_tipoval = 1, 1,0)) as P,
										SUM(IF (domaci_tipoval = 0, 1, 0)) as NETIPOVANO,
										SUM(domaci_body) as PTS,
										liga,
										rocnik
										
										FROM _tipliga_rozpisy
										WHERE 
										liga=%s
										AND rocnik=%s
										AND kolo <= %i
										GROUP BY hrac
								
										UNION ALL
										
										SELECT
										host as hrac,
										COUNT(*) as PZ,
										SUM(IF (domaci_body<host_body,1,0)) as V,
										SUM(IF (domaci_body=host_body AND domaci_tipoval = 1 AND host_tipoval = 1, 1,0)) as R,
										SUM(IF (domaci_body>host_body AND host_tipoval = 1, 1,0)) as P,
										SUM(IF (host_tipoval = 0, 1, 0)) as NETIPOVANO,
										SUM(host_body) as PTS,
										liga,
										rocnik
										
										FROM _tipliga_rozpisy
										WHERE 
										liga=%s
										AND rocnik=%s
										AND kolo <= %i
										GROUP BY hrac
							) as TMP
							
							JOIN hraci h ON h.id = TMP.hrac
							GROUP BY hrac
							ORDER BY PTS DESC, SKORE DESC, NETIPOVANO ASC, V DESC, h.prezdivka ASC
							
				) tmp,
				(SELECT @currentRank := 1, @i := 1, @lastPts:= -999, @lastScore := -999) as config
			) finalTable
On gists

sass mixin: Asset Helper Function

SCSS

mixin-asset-helper.scss #

/// Base path for assets (fonts, images...),
/// should not include trailing slash
/// @access public
/// @type String
$asset-base-path: '../assets' !default;

/// Asset URL builder
/// @access private
/// @param {String} $type - Asset type, matching folder name
/// @param {String} $file - Asset file name, including extension
/// @return {URL} - A `url()` function leading to the asset
@function asset($type, $file) {
  @return url($asset-base-path + '/' + $type + '/' + $file);
}

/// Image asset helper
/// @access public
/// @param {String} $file - Asset file name, including extension
/// @return {URL} - A `url()` function leading to the image
/// @require {function} asset
@function image($file) {
  @return asset('images', $file);
}

/// Font asset helper
/// @access public
/// @param {String} $file - Asset file name, including extension
/// @return {URL} - A `url()` function leading to the font
/// @require {function} asset
@function font($file) {
  @return asset('fonts', $file);
}

/// USAGE
/*
@font-face {
    font-family: 'Unicorn Font';
    src: font('unicorn.eot?') format('eot'),
         font('unicorn.otf') format('truetype'),
         font('unicorn.woff') format('woff'),
         font('unicorn.svg#unicorn') format('svg');
    font-weight: normal;
    font-style: normal;
  }
  
  .foo {
    background-image: image('kittens.png');
  }

  */
On gists

Nejvyssi zaznamy v kategoriích

MySql

example.sql #

select * from clanky where (kategorie, datum) in (select kategorie, max(datum) from clanky group by kategorie)





SELECT 
t1.id, 
t1.nadpis,
t2.datum,
t1.datum,
t2.kategorie
FROM clanky t1
JOIN  ( SELECT t2.kategorie, MAX(t2.datum) datum FROM clanky t2 GROUP BY t2.kategorie) t2
ON t1.kategorie = t2.kategorie
AND t1.datum = t2.datum







select * from clanky t1
where t1.id in (select max(t2.id) id from clanky t2 group by t2.kategorie)
On gists

SQL select only rows with max value on a column (2 ways) - universal

MySql MySql tricks MySql - advanced

solution.sql #

-- @link: https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1

-- Joining with simple group-identifier, max-value-in-group Sub-query
SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev


-- Left Joining with self, tweaking join conditions and filters
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;
On gists

Dynamic SQL via prepared statements

•
MySql MySql tricks MySql - advanced

example.sql #

-- 1
SET @table = "pexeso";
SET @sql:= CONCAT('SELECT * FROM',' ', @table);
PREPARE stmt FROM @sql;
EXECUTE stmt;
-- DEALLOCATE PREPARE stmt;  # neni povinne


-- 1b, lze dosadit parametry pres ?
SET @id = 1;
PREPARE stmt FROM 'SELECT * FROM category WHERE id = ?';
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt; 




-- 2, Dynamically Building SQL based on Parameters
CREATE DEFINER=`root`@`localhost` 
  PROCEDURE `GetRecentActivity`(input VARCHAR(15))
BEGIN
SET @input = input;

if @input="asc" then
    SET @sort = " order by activity_log_key asc";
elseif @input = "desc" then
    SET @sort = " order by activity_log_key desc";
else
    SET @sort ="";
end if;

SET @query = CONCAT('select * from activitylog ',@sort,' limit 0, 5');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END


-- 3, more than one column
SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.`COLUMNS` C 
WHERE table_name = 'MyTb' AND COLUMN_NAME LIKE '%whatever%' INTO @COLUMNS;

SET @table = 'MyTb';
SET @s = CONCAT('SELECT ',@columns,' FROM ', @table);

PREPARE stmt FROM @s;
EXECUTE stmt;
On gists

Triggers

•
MySql MySql tricks MySql - advanced

clanky.sql #

CREATE TRIGGER diskuse_ai AFTER INSERT ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet + 1 WHERE id = NEW.clanek;
CREATE TRIGGER diskuse_ad AFTER DELETE ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet - 1 WHERE id = OLD.clanek;
CREATE TRIGGER diskuse_au AFTER UPDATE ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet + IF(id = NEW.clanek, 1, -1) WHERE id = OLD.clanek XOR id = NEW.clanek;