Page MenuHomePhorge
Files F117761579

D5499.1775214748.diff
No OneTemporary
Actions

Authored By
Unknown
Size
2 KB
Referenced Files
None
Subscribers
None

D5499.1775214748.diff
View Options

diff --git a/src/database/migrations/2025_07_17_100000_users_views.php b/src/database/migrations/2025_07_17_100000_users_views.php
new file mode 100644
--- /dev/null
+++ b/src/database/migrations/2025_07_17_100000_users_views.php
@@ -0,0 +1,57 @@
+<?php
+
+use App\Transaction;
+use App\User;
+use App\Wallet;
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Support\Facades\DB;
+
+return new class extends Migration {
+ /**
+ * Run the migrations.
+ */
+ public function up(): void
+ {
+ // A subquery to get the all wallets with a positive credit/award transaction
+ $transactions = DB::table('transactions')
+ ->selectRaw('distinct object_id as wallet_id')
+ ->where('object_type', Wallet::class)
+ ->where('amount', '>', 0)
+ ->whereIn('type', [Transaction::WALLET_AWARD, Transaction::WALLET_CREDIT]);
+
+ // A subquery to get users' wallets (by entitlement) - one record per user
+ $wallets = DB::table('entitlements')
+ ->selectRaw('min(wallet_id) as id, entitleable_id as user_id')
+ ->where('entitleable_type', User::class)
+ ->groupBy('entitleable_id');
+
+ $select = DB::table('users')
+ ->select('users.*', 'wallets.id as wallet_id')
+ ->joinSub($wallets, 'wallets', static function ($join) {
+ $join->on('users.id', '=', 'wallets.user_id');
+ })
+ ->joinSub($transactions, 'transactions', static function ($join) {
+ $join->on('wallets.id', '=', 'transactions.wallet_id');
+ })
+ ->toRawSql();
+
+ DB::statement("create view view_payers as {$select}");
+
+ $select = DB::table('users')
+ ->select('users.*', 'user_settings.value as country')
+ ->join('user_settings', 'users.id', '=', 'user_settings.user_id')
+ ->where('user_settings.key', 'country')
+ ->toRawSql();
+
+ DB::statement("create view view_users_with_country as {$select}");
+ }
+
+ /**
+ * Reverse the migrations.
+ */
+ public function down(): void
+ {
+ DB::statement("drop view if exists view_payers");
+ DB::statement("drop view if exists view_users_with_country");
+ }
+};

File Metadata

Mime Type
text/plain
Expires
Fri, Apr 3, 11:12 AM (17 h, 8 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
18823788
Default Alt Text
D5499.1775214748.diff (2 KB)

Event Timeline