mikehins

First Row of Many Similar Ones
‍
SQL is a straightforward and expressive language, but it sometimes lacks constructs for writing queries in a simple way. It's more complicated than it should be to write a query to get, e.g., only the most expensive order for every customer of the current fiscal year. You can either use PostgreSQL's vendor-specific DISTINCT ON feature or window functions for every other database.
‍
MySQL
‍
SELECT *
FROM (
  SELECT *,  RANK() OVER(
    PARTITION BY customer_id

mikehins

# https://soft-builder.com/how-to-list-all-foreign-keys-in-mysql-database/

SELECT RefCons.constraint_schema, RefCons.table_name, RefCons.referenced_table_name, RefCons.constraint_name, KeyCol.column_name
FROM information_schema.referential_constraints RefCons
JOIN information_schema.key_column_usage KeyCol ON RefCons.constraint_schema = KeyCol.table_schema
     AND RefCons.table_name = KeyCol.table_name
     AND RefCons.constraint_name = KeyCol.constraint_name
WHERE RefCons.constraint_schema = 'DATABASE_NAME';

mikehins

// AppServiceProvider
public function boot(): void
{
  
  //...

  Blade::directive('rev', function () {
    $v = config('app.version');
    $b = exec('git rev-parse --short HEAD');
    return ($v ? 'Version ' . $v : '')  . ($b ? '<br>Build ' . $b : '');

mikehins

    protected static ?string $navigationIcon = 'heroicon-o-rectangle-stack';
    protected static ?string $navigationGroup = 'Bookings';
    protected static ?string $modelLabel = 'Booking';
    protected static ?string $navigationLabel = 'Bookings';
    protected static ?int $navigationSort = 1;

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