mikehins

# ORDER BY with nullable columns
# In MySQL they will be placed before everything. 
# But the intention of the application or the UX may need a different sort order. 
# In these cases the ordering for NULL values can be changed easily.


-- Default behaviour: NULL values placed first
SELECT * 
FROM customers ORDERBY country ASC;
-- NULL values placed first by rule

mikehins

# https://sqlfordevs.com/delete-duplicate-rows

WITH duplicates AS ( 
  SELECT id, ROW_NUMBER() OVER 
  ( 
    PARTITION BY firstname, lastname, email 
    ORDER BY age DESC 
  ) AS rownum 
  FROM contacts 
) 

mikehins

SELECT  t.my_column_1, t.my_column_2
FROM    (
         SELECT *,
                ROW_NUMBER() OVER (PARTITION BY my_column ORDER BY my_column_2 DESC) AS row_num
         FROM   my_table
        ) t
WHERE   t.row_num = 1;

mikehins

public static function definedRelations(): array
{

      return collect((new \ReflectionClass(get_called_class()))->getMethods())
          ->filter(
              fn($method) => !empty($method->getReturnType()) &&
                  str_contains(
                      $method->getReturnType(), 
                      'Illuminate\Database\Eloquent\Relations'
                 )

mikehins

Route::post('/token', function () {
  if (app()->environment() !== 'local') {
    return ['error' => 'Not authorized'];
  }
  $sanctum = new Sanctum::$personalAccessTokenModel;
  $user = $sanctum->where('token', request()->token)->firstOrFail();
  $token = User::findOrFail($user->id)->createToken(request()->token);
  return ['token' => $token->plainTextToken];
});

mikehins

# Find mysql PID
ps -ef | grep mysql
sudo kill PID

mikehins

git config core.filemode false

mikehins

SELECT participants.*, 
RANK() over ( ORDER BY total_time ) rank, 
(SELECT SUM(time) FROM `leaderboards` WHERE `participant_id` = `event_participant`.`participant_id` and `event_id` = `event_participant`.`event_id` LIMIT 1) as `total_time`, 
`event_participant`.`event_id` as `pivot_event_id`, `event_participant`.`participant_id` as `pivot_participant_id` 
FROM `participants` 
inner join `event_participant` on `participants`.`id` = `event_participant`.`participant_id` 
WHERE `event_participant`.`event_id` in (29) ORDER BY `total_time` ASC


$participants = Participant::select('participants.*',

mikehins

select concat(fks.constraint_schema, '.', fks.table_name) as foreign_table,
       '->' as rel,
       concat(fks.unique_constraint_schema, '.', fks.referenced_table_name)
              as primary_table,
       fks.constraint_name,
       group_concat(kcu.column_name
            order by position_in_unique_constraint separator ', ') 
             as fk_columns
from information_schema.referential_constraints fks
join information_schema.key_column_usage kcu

mikehins

// order array diachritic
$collator = new Collator('fr_FR');
$collator->asort($results);