Laravel Türkiye Discord Kanalı Forumda kod paylaşılırken dikkat edilmesi gerekenler!Birlikte proje geliştirmek ister misiniz?

Bu Union Sorgusunda neyi Yanlış Yapıyorum Acaba ?

koti42

Sabah mustafa abi union ile 2 tabloyu birleştirebileceğimi söylemişti union ile ilgili biraz örnek aradım nasıl yapabileceğime dair ama fazla bir bilgi bulamadım deneme yanıma yoluyla bir şeyler yapmaya çalışıyorum ama hata alıyorum.

Date ve last_pre_register_date kolonlarını tek bir kolon haline getirip sıralama yapmak istiyorum, ama başarılı olamadım

message: "SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: (select * from product_price_educations where date(date) >= 2021-12-15 and product_price_id in (26, 82, 141, 43, 137, 135, 138, 79, 227, 155, 154, 134, 133, 80, 81, 140, 139, 179, 178, 136, 190, 188, 229, 125, 126, 142, 143, ) and product_price_educations.deleted_at is null) union (select * from products where pre_register = 1 and last_pre_register_date is not null and products.deleted_at is null))"

Hata Mesajı

       $productDate2=Product::where('pre_register',1)
                    ->whereNotNull('last_pre_register_date');

                $productPriceIds25 = ProductPriceEducation::with(
                    'productPrice'
                )
                    ->whereDate('date', '>=', Carbon::now()->toDateString())
                    ->whereIn('product_price_id', ProductPrice::whereHas('product', function ($q) {
                        return $q->where('pre_register', 0);
                    })->pluck('id'))
                    ->union($productDate2)
                    ->get();

Kodlarım

Sql Çıktısı

^ "(select * from product_price_educations where date(date) >= ? and product_price_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and product_price_educations.deleted_at is null) union (select * from products where pre_register = ? and last_pre_register_date is not null and products.deleted_at is null)"

mgsmus

koti42 Her iki sorguda da select() ile sadece çekeceğiniz alanları belirlemeniz lazım, sayıları aynı olmalı ve isimleri de aynı olmalı, onun için de alias kullanmanız lazım.

koti42

mgsmus Abi çok şey istiyecem ama şöyle örnek bir laravel kodu yazar mısın acaba benim jeton anca düşüyor 😃

mgsmus

koti42

$customers = Customer::select('customer_name as name, created_at')

$union= User::select('name, created_at')
    ->union($customers);

$users= DB::query()
    ->fromSub($users, 'u')
    ->orderBy('u.created_at')
    ->get();

koti42

Teşekkürler mustafa abi

mgsmus

koti42 Union ile Eloquent özelliklerini kullanamayabilirsiniz, o yüzden DB::table() kullanarak sade bir collection çekmeye çalışın ya da yeni bir sorguda kullanmak için id'leri seçin, sonra Eloquent'e geçin. Union Eloquent'in değil DB'nin özelliği.

koti42

mgsmus Tamam abi birazdan deneyeceğim teşekkürler 🙂