Eloquent Join, Cross Join, Advanced Join Clauses, Sub-query Joins, migration schema unique two column

I am a forgetful one. So many time I have to dig into documentation that with normal developer can/often remember.
https://laravel.com/docs/5.8/queries

https://stackoverflow.com/questions/1463363/how-do-i-rename-an-index-in-mysql

https://stackoverflow.com/questions/51497890/how-to-search-case-insensitive-in-eloquent-model

Yeah damp PHP check string empty

IT should be != '' otherwise " " blank or space not empty ?
My be I have to trim it first ?

if empty( trim($locality) ) {}
($locality != '')


This is it, Why/How automatic where condition added to Eloquent ?
https://stackoverflow.com/questions/14271812/laravel-set-an-automatic-where-clause

in query() method of Model 
public function query() // newQuery in Laravel 4
{

    $query = parent::query();

    $query->where('deleted','=','0');

    return $query;
}

But I can not find any override (?) of this function in my Model or BaseModel.
It is strange.

Back to Foreign key lazy problem
https://dba.stackexchange.com/questions/168590/not-using-foreign-key-constraints-in-real-practice-is-it-ok
We (outsourcer) mostly ignore foreign key relations.

Nothing is free. Sometime not having something isn't free either. Both having and not having declared foreign keys come with costs and benefits.

The point of a foreign key (FK) is to ensure that this column over here can only ever have values that come from that column over there1. This way we can be sure we only ever capture orders for customers that actually exist, for products we actually produce and sell. A lot of people think this is a good idea.

The reason we declare them inside the DBMS is so it can take care of enforcing them. It will never ever allow in any data that breaks the rules. Also it will never allow you to get rid of data required to enforce the rules. By delegating this task to the machine we can have confidence in the integrity of the data, no matter what its source or when it was written or which application it came through. Of course this comes with a cost. The DBMS has to check the rules are being followed, for each and every row, for each and every query, all the time. This takes time and effort, which is a load on the server. It also requires the humans to submit DML in a sequence that respects the rules. No more slyly forcing in an Order, then catching up with the admin afterwards. Oh no naughty human, you must first create the Customer, then the Product (and all the prerequisite rows) and only then may you create an Order.

Without foreign keys we are much freer with what we can do, and the order in which we can do it. Problematical rows can be removed ad hoc to allow critical processes to complete. The data can be patched up afterwards, when the panic is over. INSERTs are generally a little quicker (which adds up over time) because no FKs checks are done. Arbitrary subsets of data can be pulled from the DB as desired without having to ensure all supporting data is included. And so on. This can be absolutely fine if the people involved know the system, take careful notes, have good reconciliation routines, understand the consequences and have the time to tidy up after themselves. The cost is that something, somewhere is missed one time and the database deteriorates into barely creditable junk.

Some teams put the checks in the application rather than in the database. Again, this can work. It seems to me, however, that the total server load will be much the same (or slightly higher) with this approach, but the risk of forgetting some check in a bit of code somewhere is much higher. With DRI the rule's communicated to the computer once and is enforced forever. In application code it has to be re-written with each new program.

For my two cents' worth, I'm all for foreign keys. Let the computers do what they're good at doing, like routine repetitive checking that column values match. We humans can concentrate on dreaming up new and interesting stuff. Having taken responsibility for a system a few months back I'm adding FKs to most tables. There are a few, however, where I will not be adding them. These are where we collect data from external sources. The cost of rejecting these rows is greater than the cost of accepting bad data and fixing it up later. So, for these few tables, there will be no foreign keys. I do this open-eyed, knowing we have monitoring and corrective procedures in place.

1I acknowledge the existence of multi-column foreign key constraints

I think I have to dig into Collection vs Object topic.
https://vegibit.com/laravel-hasmany-and-belongsto-tutorial/
Data Mapper is hard, M. Fowler also said that. Even it has been evolved long time, you can feel many struggle during work with business logic (object) and mapping it to Relational DB and vice versa.

DB::transaction(function () use ($response_json, $shop_id) {
...
}


A interesting case about Google Place Data short_name vs long_name
{
            "long_name" : "New York",
            "short_name" : "NY",
            "types" : [ "administrative_area_level_1", "political" ]
         },

With alpha like character this make sense, but with graphic language (caligraph, phonetic...), it seem teh same:
         {
            "long_name" : "沖縄県",
            "short_name" : "沖縄県",
            "types" : [ "administrative_area_level_1", "political" ]
         },
=> both similar, and developer mistake use short_name could lead to data error;

https://stackoverflow.com/questions/30365169/access-controller-method-from-another-controller-in-laravel-5

You can access your controller method like this:

app('App\Http\Controllers\PrintReportController')->getPrintReport();

This will work, but it's bad in terms of code organisation (remember to use the right namespace for your PrintReportController)

You can extend the PrintReportController so SubmitPerformanceController will inherit that method

class SubmitPerformanceController extends PrintReportController {
     // ....
}

But this will also inherit all other methods from PrintReportController.

The best approach will be to create a trait (e.g. in app/Traits), implement the logic there and tell your controllers to use it:

trait PrintReport {

    public function getPrintReport() {
        // .....
    }
}

Tell your controllers to use this trait:

class PrintReportController extends Controller {
     use PrintReport;
}

class SubmitPerformanceController extends Controller {
     use PrintReport;
}

Both solutions make SubmitPerformanceController to have getPrintReport method so you can call it with $this->getPrintReport(); from within the controller or directly as a route (if you mapped it in the routes.php)

 ==> In my case, I have to move common method from Controller to Model, so call Model method is ok.

https://stackoverflow.com/questions/20065697/schema-builder-laravel-migrations-unique-on-two-columns

Unique more than one column.

MySQL zerofill

https://stackoverflow.com/questions/43339228/mysql-select-not-in-from-30k-rows-fast

https://quickdraw.withgoogle.com/
https://react.rocks/example/react-sketchpad

Some math calc on MySQL
https://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql


https://www.grammarly.com/blog/its-vs-its/


Some issues with caching race condition.
https://www.drupal.org/project/drupal/issues/1014086

My problem is that, my redis cache set not update when new data changed (CRUD). I mean the race condition on algorithm logic to expire the cache.
My domain logic fetch 10 latest post and shop from one user. My solution is that, I cache all user post-shop. Because of number of user post-shop not changed frequently compared to use-case here: view user post on the map. User panning, zoom etc. on the map, so I don't want to query data every little change on user location. So caching all user posted shop seem legit.
    More detail about key logic here is that I only need two latest user post on each shop.The problem is when user update, post new review or delete one of them, I have to check to update cache or not.

https://stackoverflow.com/questions/35934068/how-to-avoid-race-condition-in-a-rails-model-that-records-payments-and-running-b

Careful with Eloquent join() vs leftJoin()
join seem ~ inner join ?

leftJoin() often have addition condition so it can cover the logic. Double check these usage, especially with count query.


 https://mattstauffer.com/blog/laravel-5.0-eloquent-attribute-casting/

hidden...