SELECT ClientID, LastName, FirstName, FROM orders
inner join Client using(ClientID)
where OrderDate like “2015%” // это надо заменить на extract(year, OrderDate) = 2015 взависимости то того что за дб
Group by ClientID, LastName, FirstName
having count(*) >= 25;create table public.profiles (
id uuid not null,
username text default concat('Пользователь #', random()*100000),
primary key (id)
);Syntax: error_page code ... [=[response]] uri;
Context: http, server, location, if in location
include /etc/nginx/sites-enabled/*;
cd /etc/nginx/sites-enabled/
products
--- id
--- name
product_attributes
--- id
--- name
product_attribute_values
--- id
--- attribute_id
--- product_id
--- value
class Product extends Model
{
use HasFactory;
protected $fillable = ['name'];
public function attributes(): BelongsToMany
{
return $this->belongsToMany(Attribute::class, 'product_attribute_value')->withPivot(['value']);
}
}class Attribute extends Model
{
use HasFactory;
protected $fillable = ['name'];
public function getValuesAttribute($value){
return collect(explode(',', $value));
}
}$a1 = \App\Models\Attribute::create([
'name' => 'Attr1',
]);
$a2 = \App\Models\Attribute::create([
'name' => 'Attr2',
]);
$a3 = \App\Models\Attribute::create([
'name' => 'Multy Attr3',
]);
$p1 = \App\Models\Product::create([
'name' => 'Product1',
]);
$p1->attributes()->attach($a1->id, [
'value' => '2012',
]);
$p1->attributes()->attach($a2->id, [
'value' => 'Джинса',
]);
$p1->attributes()->attach($a3->id, [
'value' => 'Синий',
]);
$p1->attributes()->attach($a3->id, [
'value' => 'Черный',
]);
$p1->attributes()->attach($a3->id, [
'value' => 'Зеленый',
]);
$p2 = \App\Models\Product::create([
'name' => 'Product2',
]);
$p2->attributes()->attach($a1->id, [
'value' => '2013',
]);
$p2->attributes()->attach($a2->id, [
'value' => 'Лен',
]);
$p2->attributes()->attach($a3->id, [
'value' => 'Красный',
]);
$p2->attributes()->attach($a3->id, [
'value' => 'Серобурый',
]);
$p3 = \App\Models\Product::create([
'name' => 'Product3',
]);
$p3->attributes()->attach($a1->id, [
'value' => '2015',
]);
$p3->attributes()->attach($a2->id, [
'value' => 'Лен',
]);
$p3->attributes()->attach($a3->id, [
'value' => 'Синий',
]);
$p3->attributes()->attach($a3->id, [
'value' => 'Желтый',
]);public function attributes_with_grouped_values(): HasMany{
$instance = $this->newRelatedInstance(Attribute::class);
$query = $instance->newQuery()->join('product_attribute_value', 'product_attribute_value.attribute_id', '=', 'attributes.id')
->groupBy('attributes.id', 'attributes.name', 'product_attribute_value.product_id')
->select('attributes.id', 'attributes.name', 'product_id', DB::raw('string_agg(product_attribute_value.value, \',\') as values'));
// в случае с мускул string_agg(product_attribute_value.value, \',\') заменить на group_concact
return $this->newHasMany(
$query, $this, 'product_attribute_value.product_id', 'id'
);
}public function getValuesAttribute($value){
return collect(explode(',', $value));
}$products = Product::with('attributes_with_grouped_values')->get();
foreach ($products as $product){
echo $product->name.PHP_EOL;
foreach ($product->attributes_with_grouped_values as $attribute){
dump(json_encode([$attribute->name => $attribute->values->all()]));
}
}
Product1
^ "{"Attr1":["2012"]}"
^ "{"Attr2":["\u0414\u0436\u0438\u043d\u0441\u0430"]}"
^ "{"Multy Attr3":["\u0421\u0438\u043d\u0438\u0439","\u0417\u0435\u043b\u0435\u043d\u044b\u0439","\u0427\u0435\u0440\u043d\u044b\u0439"]}"
Product2
^ "{"Attr1":["2013"]}"
^ "{"Attr2":["\u041b\u0435\u043d"]}"
^ "{"Multy Attr3":["\u041a\u0440\u0430\u0441\u043d\u044b\u0439","\u0421\u0435\u0440\u043e\u0431\u0443\u0440\u044b\u0439"]}"
Product3
^ "{"Attr1":["2015"]}"
^ "{"Attr2":["\u041b\u0435\u043d"]}"
^ "{"Multy Attr3":["\u0416\u0435\u043b\u0442\u044b\u0439","\u0421\u0438\u043d\u0438\u0439"]}"
geo $client {
default third;
10.50.0.0/16 second;
178.50.0.0/16 first;
}
server{
location / {
try_files $uri.$client $uri = 404;
}
}update records set position = (select floor(rand()*(max(position) - min(position))+min(position)) from (select * from records) as t);protected $messages = [
'nameOrganization.required' => 'Заполните поле :attribute. :input - не подходит'
];with t as(SELECT table_name, column_name, data_type, ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY column_name) as num
FROM information_schema.columns
WHERE table_schema = 'public')
select json_agg(json_build_object(num, json_build_object(column_name, data_type))) from t group by table_name;Users::select(
'users.*',
DB::raw("IF(exist (select * from admin where admin.user_id = users.id), 'admin', (select name from rank where score<=users.points order by score desc limit 1)) as rank"))
->get();