trin

Member Since 8 Months Ago

Experience Points
14,050
Total
Experience

950 experience to go until the next level!

In case you were wondering, you earn Laracasts experience when you:

  • Complete a lesson — 100pts
  • Create a forum thread — 50pts
  • Reply to a thread — 10pts
  • Leave a reply that is liked — 50pts
  • Receive a "Best Reply" award — 500pts
Lessons Completed
5
Lessons
Completed
Best Reply Awards
17
Best Reply
Awards
  • start your engines Created with Sketch.

    Start Your Engines

    Earned once you have completed your first Laracasts lesson.

  • first-thousand Created with Sketch.

    First Thousand

    Earned once you have earned your first 1000 experience points.

  • 1-year Created with Sketch.

    One Year Member

    Earned when you have been with Laracasts for 1 year.

  • 2-years Created with Sketch.

    Two Year Member

    Earned when you have been with Laracasts for 2 years.

  • 3-years Created with Sketch.

    Three Year Member

    Earned when you have been with Laracasts for 3 years.

  • 4-years Created with Sketch.

    Four Year Member

    Earned when you have been with Laracasts for 4 years.

  • 5-years Created with Sketch.

    Five Year Member

    Earned when you have been with Laracasts for 5 years.

  • school-in-session Created with Sketch.

    School In Session

    Earned when at least one Laracasts series has been fully completed.

  • welcome-newcomer Created with Sketch.

    Welcome To The Community

    Earned after your first post on the Laracasts forum.

  • full-time-student Created with Sketch.

    Full Time Learner

    Earned once 100 Laracasts lessons have been completed.

  • pay-it-forward Created with Sketch.

    Pay It Forward

    Earned once you receive your first "Best Reply" award on the Laracasts forum.

  • subscriber Created with Sketch.

    Subscriber

    Earned if you are a paying Laracasts subscriber.

  • lifer Created with Sketch.

    Lifer

    Earned if you have a lifetime subscription to Laracasts.

  • evangelist Created with Sketch.

    Laracasts Evangelist

    Earned if you share a link to Laracasts on social media. Please email [email protected] with your username and post URL to be awarded this badge.

  • chatty-cathy Created with Sketch.

    Chatty Cathy

    Earned once you have achieved 500 forum replies.

  • lara-veteran Created with Sketch.

    Laracasts Veteran

    Earned once your experience points passes 100,000.

  • 10k-strong Created with Sketch.

    Ten Thousand Strong

    Earned once your experience points hits 10,000.

  • lara-master Created with Sketch.

    Laracasts Master

    Earned once 1000 Laracasts lessons have been completed.

  • laracasts-tutor Created with Sketch.

    Laracasts Tutor

    Earned once your "Best Reply" award count is 100 or more.

  • laracasts-sensei Created with Sketch.

    Laracasts Sensei

    Earned once your experience points passes 1 million.

  • top-50 Created with Sketch.

    Top 50

    Earned once your experience points ranks in the top 50 of all Laracasts users.

  • Community Pillar

    Earned once your experience points ranks in the top 10 of all Laracasts users.

Level 3
14,050 XP
Mar
05
18 hours ago
Activity icon

Replied to Javascript , How To Verify If A Property Of An Object Exists Or Not?

if u can use babel, with standart presets, u can

if (this?.var1?.proer1) {

or

if (this?.var1?.proer1 === "hello world') {
Mar
04
1 day ago
Activity icon

Replied to How To Upload Large File In Laravel 8

open phpinfo and look, from where php.ini loaded. but, as I already said, most likely the problem is in the webserver settings

Activity icon

Replied to How To Upload Large File In Laravel 8

it is webserver limits. if u use nginx, u need set client_max_body_size 600M; for example. remember, body size not are file size.

Activity icon

Awarded Best Reply on Extract Validation Field Names On Controllers And Requests

i dont know about $request->validate in controller, but u can get all routes, controllers and validations, if u have request files.

for example, we have api route

<?php

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Route;
use App\Http\Requests\UserRequest;

Route::middleware('auth:api')->get('/user', function (UserRequest $request) {
    return $request->user();
});

and UserRequest.php

<?php

namespace App\Http\Requests;

use Illuminate\Foundation\Http\FormRequest;

class UserRequest extends FormRequest
{
    public function rules()
    {
        return [
            'login' => ['required'],
            'password' => ['required', 'min:6']
        ];
    }
}

first u need get routes:

$result = [];
$routes = Route::getRoutes(); // get all routes

/* filter by middleware, for example, we get only 'api' routes */
foreach ($routes as $route) {
    foreach (['api'] as $middle) {
        if (in_array($middle, $route->gatherMiddleware())) {
            $result[] = $route;
            break;
        }
    }
} 

after, u get rules by route

foreach ($result as $item) {
    $signature = $item->signatureParameters();
    foreach ($signature as $param) {
        if ($param->name === 'request') { // request
            $tmp = $param->getType()->getName();
            if ((new \ReflectionClass($tmp))->hasMethod('rules')) {
                dd((new $tmp)->rules()); // all rules
            }
        }
    }
}

u get dd like

array:2 [▼
  "login" => array:1 [▼
    0 => "required"
  ]
  "password" => array:2 [▼
    0 => "required"
    1 => "min:6"
  ]
]

code in sandbox: https://phpsandbox.io/n/proud-bar-qzkz-mr9gg

Mar
03
2 days ago
Activity icon

Replied to How To Deploy Laravel With Front End React Integration?

and whats probem to depoy app.js?

Activity icon

Awarded Best Reply on Composer Remove

rm -rf ./vendor
composer install
Activity icon

Replied to How To Deploy Laravel With Front End React Integration?

yes, i see it ) and what you want? who generate app.js? laravel?

Activity icon

Replied to Composer Remove

rm -rf ./vendor
composer install
Activity icon

Replied to How To Deploy Laravel With Front End React Integration?

frontend is static files only, u just upload it

Mar
02
3 days ago
Activity icon

Replied to File Storage Works Locally But Not In Production

laravel need to process this url, or it is only static files? i mean, if it only static, u can rewrite url by nginx/apache

Activity icon

Replied to MySQL Help With The Correct Join Statement

if u need all records as separate items, u need union

(
	SELECT id, 'table1' as source FROM table1 WHERE timestamp between '2020/28/2' and '2020/28/3'
)
UNION
(
	SELECT id, 'table2' as source FROM table2 WHERE timestamp between '2020/28/2' and '2020/28/3'
)
UNION
(
	SELECT id, 'table3' as source FROM table3 WHERE timestamp between '2020/28/2' and '2020/28/3'
)
Activity icon

Awarded Best Reply on Show Additional Information When The Mouse Is Over It

sorry for russian language ;)

тебе надо убрать класс hidden из второго блока, т.е. вот это

<div class="bg-purple-700 absolute bottom-0 z-10 w-full h-full bg-opacity-90 p-10 justify-center items-center hidden hover:block">

должно стать этим

<div class="bg-purple-700 absolute bottom-0 z-10 w-full h-full bg-opacity-90 p-10 justify-center items-center">

и если всё на своих местах, т.е. если у тебя все блоки отображаются, как-будто на них наведена мышь, то в общем всё в порядке. Возвращай hidden. Дальше я бы поставил hover:hovered на основной блок, тот что

<div class="shadow-lg rounded-lg overflow-hidden relative">

чтобы получилось

<div class="shadow-lg rounded-lg overflow-hidden relative hover:hovered">

таким образом родительский блок при наведении получит класс hovered, теперь просто добавь в css

.hovered .hidden{
	display:block !important;
}
Activity icon

Replied to Show Additional Information When The Mouse Is Over It

Russian obscene - my native )

Activity icon

Replied to Map Function Giving Double Array In Result

post here

dd($request->params);

and what result would you like to receive?

Activity icon

Replied to Show Additional Information When The Mouse Is Over It

sorry for russian language ;)

тебе надо убрать класс hidden из второго блока, т.е. вот это

<div class="bg-purple-700 absolute bottom-0 z-10 w-full h-full bg-opacity-90 p-10 justify-center items-center hidden hover:block">

должно стать этим

<div class="bg-purple-700 absolute bottom-0 z-10 w-full h-full bg-opacity-90 p-10 justify-center items-center">

и если всё на своих местах, т.е. если у тебя все блоки отображаются, как-будто на них наведена мышь, то в общем всё в порядке. Возвращай hidden. Дальше я бы поставил hover:hovered на основной блок, тот что

<div class="shadow-lg rounded-lg overflow-hidden relative">

чтобы получилось

<div class="shadow-lg rounded-lg overflow-hidden relative hover:hovered">

таким образом родительский блок при наведении получит класс hovered, теперь просто добавь в css

.hovered .hidden{
	display:block !important;
}
Activity icon

Replied to Extract Validation Field Names On Controllers And Requests

i dont know about $request->validate in controller, but u can get all routes, controllers and validations, if u have request files.

for example, we have api route

<?php

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Route;
use App\Http\Requests\UserRequest;

Route::middleware('auth:api')->get('/user', function (UserRequest $request) {
    return $request->user();
});

and UserRequest.php

<?php

namespace App\Http\Requests;

use Illuminate\Foundation\Http\FormRequest;

class UserRequest extends FormRequest
{
    public function rules()
    {
        return [
            'login' => ['required'],
            'password' => ['required', 'min:6']
        ];
    }
}

first u need get routes:

$result = [];
$routes = Route::getRoutes(); // get all routes

/* filter by middleware, for example, we get only 'api' routes */
foreach ($routes as $route) {
    foreach (['api'] as $middle) {
        if (in_array($middle, $route->gatherMiddleware())) {
            $result[] = $route;
            break;
        }
    }
} 

after, u get rules by route

foreach ($result as $item) {
    $signature = $item->signatureParameters();
    foreach ($signature as $param) {
        if ($param->name === 'request') { // request
            $tmp = $param->getType()->getName();
            if ((new \ReflectionClass($tmp))->hasMethod('rules')) {
                dd((new $tmp)->rules()); // all rules
            }
        }
    }
}

u get dd like

array:2 [▼
  "login" => array:1 [▼
    0 => "required"
  ]
  "password" => array:2 [▼
    0 => "required"
    1 => "min:6"
  ]
]

code in sandbox: https://phpsandbox.io/n/proud-bar-qzkz-mr9gg

Activity icon

Replied to Connecting TablePlus To Localhost

sorry, its about sail. ok, i see, host up, but u try connect with root login, can u try with laravel/laravel login/password? and try to connect from console, like

mysql -hmysql -ularavel -p
Activity icon

Replied to Connecting TablePlus To Localhost

DB_HOST must be localhost in your case

Activity icon

Started a new Conversation Push Notify Extension For Laracasts Discuss (chrome & Firefox)

as I told earlier, i wrote small chrome extension, to add feature "follow by push" on threads. this extensions approve in google web store and now it exist to install all clients. also, i modified code and publish extension as firefox addon too. it seemed simple. not really ) @sr57, only for u bro ) and for experience, of couse.

i moved to webpack5 and refactored options page with vue (not preact, as early). if u use chrome or firefox, only u need to install:

chrome: https://chrome.google.com/webstore/detail/laracasts-discuss-push-no/fggkellghninlgbggeioklhlflfmjkfg

firefox: https://addons.mozilla.org/ru/firefox/addon/laracasts-discuss-push-notify/

play with sources:

https://github.com/trin4ik/laracasts-discuss-push-notify

finnaly extension for firefox and chrome has small different, but has. so if u want play with sources, u need build for firefox and chrome separate

git clone https://github.com/trin4ik/laracasts-discuss-push-notify 
cd laracasts-discuss-push-notify 
yarn
yarn chrome # or yarn firefox

and for all, who want write self extension, i recommended write first to firefox, after modify for chrome. chrome has more flexible api, firefox has more stronger.

extension consists of three parts:

  • background script. wait for subscribe, check new answers, 'talk' with other parts
  • content script. inject in laracasts.com, observe and modify dom, handle event
  • options page. vue component for enabled/disabled extension, list of subscribes etc
Activity icon

Replied to Need Suggestion What To Use (API Or WEB) Next In Laravel Project?

no best practice for your question, only your choice. im always use only api. my laravel not generate html. all front-end generated by reACt, vue, angular etc. payment for it -- i use only 50% power of laravel. in yout case i recommended use both, api and web. for like use api, for content livewire

Feb
28
5 days ago
Activity icon

Replied to Download Large Files In Laravel

laravel just php framework, php very bad case for stream big files. u need use web server (like nginx), who do this work great. if u need preprocess with php (auth, statistics, etc), use http headers like x-accel-redirect in nginx or x-sendfile in apache. whats they do? after all your preprocess, php send x-accel-redirect to nginx and close php-process. nginx, having received header, redirect user to internal storage (closed from http root, but nginx can stream files, when get x-accel-redirect)

https://www.nginx.com/resources/wiki/start/topics/examples/xsendfile/

why php bad in this case? because php not close process, while send file. and if client have bad internet, your server go away with many php process.

Activity icon

Replied to SSH Tunnel - Connect From Local To Remote Server

after several unsuccessful attempts, i stops on GSTM works without problems.

Feb
26
1 week ago
Activity icon

Replied to How To Convert Time In Js

use dayjs and be happy. his save your time.

const time = dayjs(1614358672000).tz("America/New_York").format('M/D/YYYY, h:mm:ss A')
Activity icon

Replied to Some Stuff For Local Residents

https://blog.chosunghyun.com/porting-a-chrome-extension-to-firefox-add-on/ as I said, 90% of the code can be maintained without changes

edit: u can see this report for my extension: https://www.extensiontest.com/test/dfd40290-7812-11eb-bd32-6717103253a2 as i see, it is some problem with js syntax (propably my love to es6) and need some specific changes to manifest.json, and it is all. i will do this changes and try to post firefox addon in couple of days

Activity icon

Replied to Some Stuff For Local Residents

December 28, 2009, im not are addon/extension developer and do this work only for fun, but as can i see, this information is out of date.

Activity icon

Replied to Some Stuff For Local Residents

respect firefox, but mozilla are very strange guys) I'll see about Firefox addon, as long as i remember, Firefox add-on and Chrome add-on may be the same on 90%

Activity icon

Replied to What Is The Best Practice To Persist Auth User Info In SPA?

localstorage good choice.

  • u save token in localstorage, when user auth
  • u check token, when app start (send query for userinfo with token)
  • u add header like “x-token” for all xhr query, if token good.
Activity icon

Replied to MySQL Or Eloquent

i love mysql from 3.23 version, and when i move to laravel, i incredulously move to eloquent. today, as before it easier to me write raw sql query. and for myself i decides like @jlrdw: use eloquent model, if need performance — write raw.

Feb
25
1 week ago
Activity icon

Replied to Run Code As Per Github Branches

simpler do this with subdomain, not subdirectory. any way, u can clone necessary branch with

 git clone -b test1 [email protected]:my/repo.git

for example, your project in /var/www/abc.com/, configure nginx like

server {
    listen       80;
    server_name   "~^(?<branch>.+)\.abc\.com$" ;

	root /var/www/abc.com/master;

	if (-d /var/www/abc.com/$branch) {
		root /var/www/abc.com/$branch;
	}

   …
}

and u can automatically clone new branches with github hooks

Activity icon

Replied to If..ElseIf..Else Returns With If Always After Compared To A Value

u are welcome. switch not are best&true solution for always, but in many cases it can clear and simplify u code. and dont forget about break )

Activity icon

Replied to If..ElseIf..Else Returns With If Always After Compared To A Value

a little off topic, for same case i recommended use switch

switch ($kat_id) {
	case: 1 {
		echo 'Logic for 1';
		break;
	}
	case: 2 {
		echo 'Logic for 2';
		break;
	}
	default: {
		echo 'Do Nothing!';
	}
}

but if u confuse = and ==/===, u probably forget to put break

Activity icon

Awarded Best Reply on What's The Best Way To Automatically Insert Slashes '/' In Date Fields

ok, lets do this in 2 step. first, we need validate input and allow input only in our regexp (actually 3 regexp, for full date, month-year and year-only)

<div id="app">
  <input :value="date" @input="onInput" />
  <div>{{format}}</div>
</div>
const DATE_FORMAT = {
  null: new RegExp('^$'),
  'full': new RegExp('^[0-9]{1,2}(\/([0-9]{1,2}(\/([0-9]{1,4})?)?)?)?$'),
  'month-year': new RegExp('^[0-9]{1,2}(\/([0-9]{1,4})?)?$'),
  'year-only': new RegExp('^[0-9]{1,4}$'),
}

const Counter = {
  data () {
    return {
      date: "",
      format: "null"
    }
  },
   methods: {
     onInput (e) {
       let text = e.currentTarget.value || ""
       
       const test = Object.entries(DATE_FORMAT).find(regexp => {
         if (regexp[1].test(text)) { // map for regexp, find correct
           return regexp[0]
         }
       })
       
       if (test) { // if regexp find, set `this.date` and `this.format`
         this.format = test[0]
         this.date = text
       }
       
       this.$forceUpdate() // force update for rerender
     }
   }
}

Vue.createApp(Counter).mount('#app')

now our input always regexp format. next, we need to add slashes. and we must understand on the fly, what the format we need. example, where user inputs 11/11, what we need to do? add slashes? or user want enter 11/1111? for this cases, make new method and enjoy with him. example:

dateAutoSlashes (e) {
       if (e.inputType === 'deleteContentBackward') return // if backspace, break. 
       if (this.date.length === 2) { // add slashes after two digits
         this.date = this.date + '/'
       }
       if (this.date.length === 5 && parseInt(this.date.substr(3)) <= 12) { // add slashes after second two digits only if his lower than
         this.date = this.date + '/'
       }
     }

and call it from onInput, after this.date = text

u can play with both steps on

https://codepen.io/trin4ik/pen/NWbXEMV?editors=1010 step 1 https://codepen.io/trin4ik/pen/GRNywGX?editors=1010 step 2

Activity icon

Replied to What's The Best Way To Automatically Insert Slashes '/' In Date Fields

note, in example i use <input :value="date" @input="onInput" />, not <input :model="date" /> because we need processed input before set this.date. my vue experience is small and was long ago, probably there is a prettier way

Activity icon

Replied to What's The Best Way To Automatically Insert Slashes '/' In Date Fields

ok, lets do this in 2 step. first, we need validate input and allow input only in our regexp (actually 3 regexp, for full date, month-year and year-only)

<div id="app">
  <input :value="date" @input="onInput" />
  <div>{{format}}</div>
</div>
const DATE_FORMAT = {
  null: new RegExp('^$'),
  'full': new RegExp('^[0-9]{1,2}(\/([0-9]{1,2}(\/([0-9]{1,4})?)?)?)?$'),
  'month-year': new RegExp('^[0-9]{1,2}(\/([0-9]{1,4})?)?$'),
  'year-only': new RegExp('^[0-9]{1,4}$'),
}

const Counter = {
  data () {
    return {
      date: "",
      format: "null"
    }
  },
   methods: {
     onInput (e) {
       let text = e.currentTarget.value || ""
       
       const test = Object.entries(DATE_FORMAT).find(regexp => {
         if (regexp[1].test(text)) { // map for regexp, find correct
           return regexp[0]
         }
       })
       
       if (test) { // if regexp find, set `this.date` and `this.format`
         this.format = test[0]
         this.date = text
       }
       
       this.$forceUpdate() // force update for rerender
     }
   }
}

Vue.createApp(Counter).mount('#app')

now our input always regexp format. next, we need to add slashes. and we must understand on the fly, what the format we need. example, where user inputs 11/11, what we need to do? add slashes? or user want enter 11/1111? for this cases, make new method and enjoy with him. example:

dateAutoSlashes (e) {
       if (e.inputType === 'deleteContentBackward') return // if backspace, break. 
       if (this.date.length === 2) { // add slashes after two digits
         this.date = this.date + '/'
       }
       if (this.date.length === 5 && parseInt(this.date.substr(3)) <= 12) { // add slashes after second two digits only if his lower than
         this.date = this.date + '/'
       }
     }

and call it from onInput, after this.date = text

u can play with both steps on

https://codepen.io/trin4ik/pen/NWbXEMV?editors=1010 step 1 https://codepen.io/trin4ik/pen/GRNywGX?editors=1010 step 2

Activity icon

Started a new Conversation Some Stuff For Local Residents

hi guys, im newbie in this community, and im glad to be here. i see a lot of people helping others, and I also try to help as much as possible. i have been doing web development for a long time but recently with laravel. and I often read topics that I do not answer, but I am interested in other people's answers

after a week, I realized that I was spending a lot of time tracking responses in my discussions. i constantly visit the site and update it. subscribing to updates by email is not my way, it is not convenient for me.

after digging around in the laracasts code, I wrote a small Chrome extension that adds the ability to subscribe to threads using push notifications rather than email.

at the moment I have posted my extension and it is being verified by google. of course I will write more when this is posted. but if suddenly someone is ready to try the extension now, I would be grateful.

need google chrome, developer mode and desire) clone code, install unpacked extension and use it. its ONLY local extension, no one byte of data sended in external servers. extension use system notification center, i checked his work in macos and ubuntu.

https://github.com/trin4ik/laracasts-discuss-push-notify

Activity icon

Replied to What's The Best Way To Automatically Insert Slashes '/' In Date Fields

why u not using type="date" input or many masked-input package?

Feb
24
1 week ago
Activity icon

Replied to Changing Terminals Name Mac

echo $SHELL

or

ls -la ~/ | grep 'profile'
Feb
23
1 week ago
Activity icon

Replied to Does Laravel Disconnect To Database After Each Query?

stop php-fpm, restart mysql, look for sleep process. start php-fpm, look for sleep process. update some pages, look for sleep process. i think it php mechanic, but it not are problem.

Activity icon

Replied to Does Laravel Disconnect To Database After Each Query?

run

dd(DB::getPdo());

and look to PERSISTENT attribute. seems to laravel use persistent connection

Activity icon

Replied to Join Two Queries At The Model Level

f*ck... i wrote big text here and some key-shurtcut reload the page, i lost all text (( in short,

NO NEED USE UNION ON THIS QUERY

it is my mistacke ) more precisely, I was not entirely right and not in all cases. I'm used to working with a heavy load and I'm used to the fact that the mysql does not know how to optimize OR. i always use UNION or change structure or use many point query vs one big union. why? because if we use OR operator on different fields, mysql not use any index of them. i add 1 000 000 items, and look to EXPLAIN.

UNION

mysql> ( SELECT COUNT(1) FROM trello_cards WHERE is_archived = 1) UNION ( SELECT COUNT(1) FROM trello_cards WHERE is_archived = 0 AND list_id = 1);
+----------+
| COUNT(1) |
+----------+
|   500093 |
|   250567 |
+----------+
2 rows in set (0.38 sec)

mysql> EXPLAIN ( SELECT COUNT(1) FROM trello_cards WHERE is_archived = 1) UNION ( SELECT COUNT(1) FROM trello_cards WHERE is_archived = 0 AND list_id = 1)\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: trello_cards
   partitions: NULL
         type: ref
possible_keys: is_archived
          key: is_archived
      key_len: 8
          ref: const
         rows: 500517
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: trello_cards
   partitions: NULL
         type: index_merge
possible_keys: is_archived,list_id
          key: is_archived,list_id
      key_len: 8,8
          ref: NULL
         rows: 250258
     filtered: 100.00
        Extra: Using intersect(is_archived,list_id); Using where; Using index
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary
3 rows in set, 1 warning (0.00 sec)

i ran query many times, always near 0.4s. reasonable explain, use index in both query, in second create intersect index on the fly. this means we can сreate him for best performance.

OR operator

mysql> SELECT COUNT(1) FROM trello_cards WHERE is_archived = 1 OR list_id=1;
+----------+
| COUNT(1) |
+----------+
|   750660 |
+----------+
1 row in set (0.22 sec)

mysql> EXPLAIN SELECT COUNT(1) FROM trello_cards WHERE is_archived = 1 OR list_id=1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trello_cards
   partitions: NULL
         type: ALL
possible_keys: is_archived,list_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1001035
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

more fast query, but need toread the whole table. if we create index by is_archived, ist_id, we get best performance for union and query took near 0.15s

mysql> ( SELECT COUNT(1) FROM trello_cards WHERE is_archived = 1) UNION ( SELECT COUNT(1) FROM trello_cards WHERE is_archived = 0 AND list_id = 1);
+----------+
| COUNT(1) |
+----------+
|   500093 |
|   250567 |
+----------+
2 rows in set (0.13 sec)

mysql> EXPLAIN ( SELECT COUNT(1) FROM trello_cards WHERE is_archived = 1) UNION ( SELECT COUNT(1) FROM trello_cards WHERE is_archived = 0 AND list_id = 1)\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: trello_cards
   partitions: NULL
         type: ref
possible_keys: is_archived,is_archived_2
          key: is_archived
      key_len: 8
          ref: const
         rows: 500517
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: trello_cards
   partitions: NULL
         type: ref
possible_keys: is_archived,list_id,is_archived_2
          key: is_archived_2
      key_len: 16
          ref: const,const
         rows: 500517
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary
3 rows in set, 1 warning (0.00 sec)

BUT

  • if we add ORDER BY to the union, we lost all performace. i try use ORDER BY id, not by created_at (it same result, but by id faster), but it is not worked. mysql try to sort all temp table, it is slow.
  • most delicious, if we add customer_id in where, mysql use customer_id index in both cases, because customer_id very thin index. more thin is_archived (1,2,3,4 vs 0,1). and we get same performance to query used UNION and OR, because mysql can use other index, BUT if we added ORDER BY, UNION is dead ;)))

look, first i ran aggregation query, for count all items

mysql> ( SELECT COUNT(1) FROM trello_cards WHERE is_archived = 1 AND customer_id=2) UNION ( SELECT COUNT(1) FROM trello_cards WHERE is_archived = 0 AND list_id = 1 AND customer_id=2);
+----------+
| COUNT(1) |
+----------+
|   125552 |
|    62707 |
+----------+
2 rows in set (0.38 sec)

mysql> SELECT COUNT(1) FROM trello_cards WHERE (is_archived = 1 OR list_id=1) AND customer_id=2;
+----------+
| COUNT(1) |
+----------+
|   188259 |
+----------+
1 row in set (3.06 sec)

in EXPLAIN we see same picture, reasonable, use index etc, but without EXPLAIN we get different results.

and now add ORDER BY and take away COUNT

mysql> ( SELECT id, customer_id, is_archived, list_id FROM trello_cards WHERE is_archived = 1 AND customer_id=2) UNION ( SELECT id, customer_id, is_archived, list_id FROM trello_cards WHERE is_archived = 0 AND
list_id = 1 AND customer_id=2) ORDER BY id DESC LIMIT 10;
+---------+-------------+-------------+---------+
| id      | customer_id | is_archived | list_id |
+---------+-------------+-------------+---------+
| 1058077 |           2 |           1 |       2 |
| 1058074 |           2 |           1 |       1 |
| 1058071 |           2 |           1 |       2 |
| 1058063 |           2 |           1 |       2 |
| 1058053 |           2 |           1 |       1 |
| 1058052 |           2 |           1 |       1 |
| 1058038 |           2 |           1 |       2 |
| 1058037 |           2 |           0 |       1 |
| 1058035 |           2 |           1 |       2 |
| 1058029 |           2 |           0 |       1 |
+---------+-------------+-------------+---------+
10 rows in set (0.80 sec)

mysql> SELECT id, customer_id, is_archived, list_id FROM trello_cards WHERE (is_archived = 1 OR list_id=1) AND customer_id=2 ORDER BY id DESC LIMIT 10;
+---------+-------------+-------------+---------+
| id      | customer_id | is_archived | list_id |
+---------+-------------+-------------+---------+
| 1058077 |           2 |           1 |       2 |
| 1058074 |           2 |           1 |       1 |
| 1058071 |           2 |           1 |       2 |
| 1058063 |           2 |           1 |       2 |
| 1058053 |           2 |           1 |       1 |
| 1058052 |           2 |           1 |       1 |
| 1058038 |           2 |           1 |       2 |
| 1058037 |           2 |           0 |       1 |
| 1058035 |           2 |           1 |       2 |
| 1058029 |           2 |           0 |       1 |
+---------+-------------+-------------+---------+
10 rows in set (0.00 sec)

momentally OR query and little slowly UNION. why? because mysql get customer_id index and it easely filtered and ordered him, than union itself table, put result to temp, sort on disk etc.

Finnaly ) first, thank u @rodrigo.pedra and @gianmarx im pleased to update my knowledge. second, i get next results:

  • it is normally to use OR on different field in query. remember, based index MUST be not on this fields (in our case, based index is customer_id, and OR operator on is_archived and list_id), or query will become slowly
  • for aggregation query (counting, group by etc) UNION most performance
  • regularly update your knowledge ) at some point I thought "what if my data is outdated and mysql has long learned to optimize OR?"

if after publishing I lose my text again, I will go cry

Activity icon

Replied to Join Two Queries At The Model Level

of course, it takes some time

Activity icon

Replied to Join Two Queries At The Model Level

thanks for your work, i insert in this structure big dataset and post what will I get. filesort etc can be avoided

Activity icon

Replied to Join Two Queries At The Model Level

@rodrigo.pedra thank for EXPLAIN, and what u see in EXPLAIN? i see it no indexes on table trello_cards. and in both case, u read all table.

Activity icon

Replied to Get Input Default Value Without Typing Or Submit

use @input. not @change. this is a global advice for <input type="text" />, but i think it will helps in your case

Activity icon

Replied to Join Two Queries At The Model Level

$list = TrelloList::where('name','DONE')->first();

$union = [];

$union[] = TrelloCard::query()
	->where('is_archived', 1);
$union[] = TrelloCard::query()
	->where('is_archived', 0)
	->where('list_id', $list->id);

$result = $union[0];
foreach ($union AS $k=>$v) {
	if ($k > 0) $result->union($v);
}

$result->orderBy('id', 'desc');
$result->limit(6);

looks most performance for this case. first, u use simple where, withour OR. second, in case by @rodrigo.pedra (with all respect), u select all done trellos $query->orWhereIn('list_id', TrelloList::select('id')->where('name', 'DONE')); both of them it is pandora box on hiload

Activity icon

Replied to Join Two Queries At The Model Level

@gianmarx can u post EXPLAIN for finally query? i think it very bad practice to use operator OR on this case. your first case with UNION most performance

Activity icon

Replied to Join Two Queries At The Model Level

u need get rid of order by inside unions.

Activity icon

Replied to Join Two Queries At The Model Level

can u post here ->toSql()?