This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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 : ''); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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' | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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]; | |
}); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Find mysql PID | |
ps -ef | grep mysql | |
sudo kill PID |
NewerOlder