HELP with join/union query

Hi there - I need some serious help/direction. I have two tables: students -id -site_id -name -enter_date -exit_date student_meals -id -site_id -student_id -meal_type_id (1, 2, or 3) -date_served I need two arrays: All students enrolled on the requested 'serviceDate' ('serviceDate is between their enter_date and exit_date) that DO NOT have a meal_type_id of the requested me

mpk123's avatar
mpk123's avatar mpk123Laravel
1

Pagination question with query()

Hi, I have a simple method that handles pagination with search. I was wondering why using Guild::query() does not with pagination. Using this method below gives me the error of Call to undefined method links(). However, not using query() and handling it separately works fine. What's the reason behind this? Is there a better way to write this method below? Thanks for any advice.

n212's avatar
n212's avatar n212Laravel
4

Eloquent returns different results than MySQL Workbench query

I am building an analytics dashboard for my work. The user selects metrics on the front end that builds a query in Eloquent, that is then executed and results are spit out in an HTML table back on the front end. This is the query in question, generated by Eloquent (where clauses changed for client privacy, we do not actually work with Google lol): SELECT DISTINCT Week(display.d

rgoodmanFFE's avatar
rgoodmanFFE's avatar sr57Eloquent
6

Model function is not passing query results to view

Hello everyone! I'm new in this community and with Laravel. I'm going to create an application that retrieve a lot of numeric data from a database to then manipulate it and make some math operations. I'm facing some troubles with a function and it's not passing query results to the view. Here is my code: MODEL <?php namespace App\Models; use Illuminate\Database\Eloquent\Fa

josue_zacaula's avatar
josue_zacaula's avatar josue_zaca...Laravel
9

Where auth()->id() Query result

I have this query // Item.php dd($this->assignments(function ($assignment) { return $assignment->where('user_id', auth()->id()); })->get()); I opened it in 2 different browsers, although the result is one item belongs to one user It gives the same results on both users/browsers as in photo https://ibb.co/kc52jP9

lifesound's avatar
lifesound's avatar SinnbeckEloquent
9

Help with complex eloquent query

I have the below tables users id name games id name tournaments id name game_id leaderboards id user_id game_id tournament_id points I believe the tables are self-descriptive, the leaderboards table holds data of tournaments & games played by a user and the points they got. I want to display a paginated public table on the frontend that gets data

Esirei's avatar
Esirei's avatar sos99Code Review
2

WhereHas adding where Null condition in query

$data = Schedule::whereHas('bus.cargos',function($query) use($request){ $query->where('cargo_type_id',$request->cargo_type); }) ->toSql(); dd($data); it gives this query select * from `schedules` where exists (select * from `buses` where `schedules`.`bus_id` = `buses`.`id` and exists (select

Stallyons Tester's avatar
Stallyons Tester's avatar Stallyons...Eloquent
2

Which version of Laravel supports Sql Server 2008 Query Builder?

Laravel latest version only supports Query Builder for Sql Server 2012 and above, I need Query Builder support for Sql Server 2008, what version of laravel still supports Query Builder for Sql Server 2008? Thanks.

Vikar's avatar
Vikar's avatar SinnbeckLaravel
1

General best practive question: Javascript or query?

I'm currently using a prop that is a query on a model 'student' that returns all students currently enrolled. A property of student is homeroom and I also need an array of each distinct homeroom of currently enrolled students. Is it better to perform another query on the student model to make an additional 'homeroom' prop? Or is it better to use javascript to make it from the

mpk123's avatar
mpk123's avatar MohamedTam...Requests
1

No query results for model

am trying to get user followers post but i am getting this error No query results for model [App\Models\Idea] {"id":1,"user_id":1,"followable_type":"App\\Models\\User","followable_id":18,"accepted_at":"2022-07- am using this package https://github.com/overtrue/laravel-follow $following = $this->user->followi

ene's avatar
ene's avatar jlrdwLivewire
1

Trying to convert my MySQL query to Query builder - Laravel

Hello, currently working on an employee management system as my internship project, I'm attempting to write a query that finds the user's work anniversary for the current month and considers a leap year as well, so here is my attempt for the MySQL query, Edit: Sample table: Table "*emp_detail*": | emp_no | join_date | | -------|------------| | 1 | 2002-06-10 |

M@rty's avatar
M@rty's avatar M@rtyLaravel
6

Query with eager loading with sum

I have an eloquent model setup like this (it's many to many relationship) user id | name habit id | name | impact habit_user id | user_id | habit_id and I want to have all the users sorted by user completed habit's impact's sum in descending order I tried this but it gives weird results User::with([ 'habitUsers' => [ 'habit' => fn($q) => $q-

ramirushi7997's avatar
ramirushi7997's avatar ramirushi7...Eloquent
1

Query builder result not the same as SQL query result

Hello, I'm having some issues with Laravel's query builder, I don't have the same results when I run the query in phpMyAdmin using pure SQL. Here's my query with Laravel : $filterResult = DB::table('users') ->where(function ($query) use ($request) { $string = $request->get('search'); $query->where('name','like','%'.$string.'%')

KLassiux's avatar
KLassiux's avatar KLassiuxLaravel
4

Convert query

Hi, i need convert this query to SQL, I am very new developer. $contents = Publish::select('id', 'time_to_air', 'date' ,'position') ->where('emission_id', $request->emission->id)->with(['files' => function($query) { $query->select('url', 'md5'); }])->orderBy('position', 'ASC') ->get(); Thanks

mutant3's avatar
mutant3's avatar tisuchiEloquent
3

Laravel Relationship Query Did not work

I have two table 1. applications 2. users --applications-- table contains id,user_id,categroy,status | I want to filter applications table user name and also application column name $applications = Application::with(['user'=>function($query) use ($request){ $query->where('name','like','%'.$request->name.'%'); }])->when($request->category,fun

Jarjis's avatar
Jarjis's avatar vincent150...Eloquent
23

laravel eloquent query

guys I wrote a query I have gymClientPurchase.appointment there are appointments in it and the relation is one too many I need to send a request: status = Pending if there is one appointment Completed and other appointments are Pending it should return the appointments pending and completed and if the request status = Completed and all the appointments are completed it shouldn

eliekhazzaka's avatar
eliekhazzaka's avatar eliekhazza...Laravel
0

Adding brackets to scope query

I have the query below and need to wrap the first query ( $search) in parenthesis and can't figure out how. public function scopeFilter($query, array $filters) { $mealType = $filters['mealType'] ?? null; $serviceDate = $filters['serviceDate'] ?? null; $search = $filters['search'] ?? null; $query ->when($search, function ($quer

mpk123's avatar
mpk123's avatar mpk123Eloquent
1

Laravel query multiple models with ORM

I'm trying to make a query that includes to related tables/models: Post Model: public function user() { return $this->belongsTo(User::class); } Post Controller: 'posts' => Post::with('user')->where(user->approved)->where('approved' , true)->latest(), The code above is wrong of course but I want to achieve something similar. To list all the po

mtdesigners's avatar
mtdesigners's avatar mtdesigner...Laravel
8

passing 2 parameters in a scopeFilter query

How do I go about passing two parameters in a filter query? The below works but I want to use the $filters[$serviceDate ] instead of hardcoding the date in the last line 'filters' => Request::all('serviceDate', 'mealType'), public function scopeFilter($query, array $filters) { $query ->when( $filters['mealType'] ?? null, function ($query, $mealType

mpk123's avatar
mpk123's avatar mpk123Laravel
1

Laravel Query Condition

I've show method in controller that gets some status from the log table,Problem is sometimes this status didn't created yet so it give me Attempt to read property "user" on null so any idea how can i make condition on it to be !=null public function show($id) { $status = Log::where('permit_id', $id)->orderBy('id', 'DESC')->latest()->first()-&

karimali1337's avatar
karimali1337's avatar RickyReyes...Laravel
13

Laravel Query Builder where

Hi, i cant find the right query for this: Table Producst ad_id| property_id | property_value_id 69 | 4 | 1 69 | 7 | 6 69 | 6 | 3 67 | 4 | 1 67 | 6 | 3 67 | 7 | 6 67 | 7 | 5 71 | 4 | 1 71 | 7 | 5 72 | 4 | 1 72 | 7 | 6 72 | 6| 3 ... I need to search (where property_id = 4 and property_value_id = 1) and (where property_id = 7 and property_value_id = 6) and (where property_id

Huumer's avatar
Huumer's avatar gzaiLaravel
5

Laravel Belongstomany Eloquent query

I have two models, User and School; they both return belongs to many of each of them. I'm trying to return a list of Users that has the same School as the user logged in. USER MODEL public function school(){ return $this->belongsToMany(School::class, 'school_user'); } SCHOOL MODEL public function user(){ return $this->belongsToMany(User::class, 'sc

raphyabak's avatar
raphyabak's avatar raphyabakEloquent
2

Improving a Laravel where query execution time

Hi, I currently have the following two tables devices which many columns but the one parameter being used in the where clause is imei. This column is set to unique(). device_logs which has other fields but the parameter being used in the where clause is device_imei. Currently I have around 50 entries in the devices table and ~128,000 entries in the device_logs table. Average ex

lukegalea16's avatar
lukegalea16's avatar lukegalea1...Laravel
6

Is the delete()-method of the query builder firing an deleting-event?

Hello everybody, given the situation where I have a model with a relation like this: /** * @return MorphMany */ public function notifications(): MorphMany { return $this->morphMany(Notification::class, 'notifiable')->orderBy( 'created_at', 'desc' ); } Now I'd like to delete all related notifications and,

ahoi's avatar
ahoi's avatar sr57Laravel
1

Inner relation where query

Hello, I'm fresh laravel developer and have problem with querying inner relation data. I want to get route with orders and products for this route, same order sometimes can be assigned to different route, so order and product have to have route_id and i need to have two orders for different routes. Structure of db looks like this: routes route_id orders order_id route_id pr

przemokon's avatar
przemokon's avatar przemokonEloquent
4

Get ID of model inside eloquent query

Is there any way to get the ID inside the query? I don't know how to explain this fluently, but I'm trying to grab the product options for each product, but i need to use the product ID inside the with statement to do that. Something like that but $idOfModel should obviously be the id of each product. $product = $product->with('attributes', function($q) { $q->

Stelikas's avatar
Stelikas's avatar MohamedTam...Eloquent
1

How to structure this query?

I have three models Office, Desk, Reservation. An Office can have many Desks and a Desk belongs to an Office. A Desk can have many Reservations and a Reservation belongs to a Desk. What I want to achieve now, is a query with such kind of result: Office 1: 2022-06-10 -> 5 reservations, 2022-06-05 -> 2 reservations Office 2: 2022-06-10 -> 1 reservations, 2022-06-05 ->

wikorl's avatar
wikorl's avatar sr57Eloquent
7

Eloquent query with COALESCE, IF, complex ON clause

Hello, i'd like to ask, how would you write the following query in Laravel Eloquent? Please note the COALESCE, IF, and the complex LEFT JOIN ... ON (... AND ...). SELECT COALESCE(IF(customer_group_id=6, prices.price, NULL), products.price) AS finalPrice, prices.customer_group_id, products.*, product_translations.* FROM `product_categories` LEFT JOIN `products` ON `product_cate

aetonsi's avatar
aetonsi's avatar SilenceBri...Eloquent
2

Filter query breaks image loading with Spatie

Hi there! So I have an issue where the images won't load with Spatie Methods, when the code below is uncommented: $theme = $filters['theme'] ?? 'light'; $device = $filters['device'] ?? 'desktop'; $query->with('imageVariations', function($query) use ($theme, $device) { $query->join('site_variations', 'site_variation_id', 'site_variations.id')

aurelianspodarec's avatar
aurelianspodarec's avatar aureliansp...Laravel
2

How to write a query with a dynamic withCount() ?

Hello, Among the tables, three are interesting to understand my problem. 1rst table : states (id, name, color) 2nd table : sessions (id, date, state_id) 3rd table : students (id, name) The number of states is dynamic (in the database), so I can add some new states if needed. When I display the view of a student, I want to display some statistics with the number of sessions of

vincent15000's avatar
vincent15000's avatar vincent150...Eloquent
8

translate raw query for eloquent

I'm having some trouble with translating the following SQL query for better reading in eloquent: SELECT @number tested_number, 7 - LENGTH(nums.num) common_digits, tickets.* FROM tickets JOIN (SELECT 1 num UNION SELECT 10 UNION SELECT 100 UNION SELECT 1000 UNION SELECT 10000 UNION SELECT 100000) nums WHERE @number DIV nums.num = tickets.ticketN

adrian_cmd's avatar
adrian_cmd's avatar adrian.cmdEloquent
10

raw sql query

Hello I have employees table and emplyees_departements table and departements table employees table structure id name etc... emplyees_departements table structure id emp_id department_id departments table structure id name etc... i need a query using raw sql to get me this format (query to get all employess and each of them has a list of departments he works at) [ [ emp_id

JhonD's avatar
JhonD's avatar automicaLaravel
3

Create Query based on User selection

Hi! I am working on a project with Laravel 8 and one of the requiraments is to export data to Excel I found the library Laravel Excel and it is working fine. Then I have created a view where the user selects a table then It show all related tables to it and the user can continue selecting the related tables he wants after that the user selects all attributes he needs from each

asss02's avatar
asss02's avatar asss02Code Review
2

Query relationship table with column that its data is an parsed array(1,2,3,4,5,6)

I have a string column in the table that have this kind of data // 1,2,3,4,5,6 comma separated data, supposedly this id has equivalent value to the another table. I have the solution that I can get the data of the mentioned ids but its a bit mess. This is the step that I have solution Get all data first Loop all the data Convert the column that has comma separated data to arra

peeknot's avatar
peeknot's avatar vincent150...Laravel
1

how to validate query parameters of a get request?

I am using form request validation on post requests. Is it possible to do the same thing on a get request with query parameters? Now i'm validating it like this, inside of a model's scope. public function scopeFilter($query, array $filters) { if ( isset($filters['sort']) && isset($filters['by']) && in_array($filters['sort'], ['name->en', 'name->k

DavidBuchukuri's avatar
DavidBuchukuri's avatar iftekhsLaravel
3

I want to run a query after registration

How do I run a query builder after registrating (and not logging in)?

ilex01's avatar
ilex01's avatar ilex01Laravel
6

Re-writting a query for pagination

Hi kindly help re-write this script so that i can compact a paginated result. public function property(Request $request) { $query = Property::query(); if ($request->status) $query->where('status', '=', $request->status); if ($request->type) $query->where('type', '=', $request->type); if ($request->bedrooms) $query->where('bedrooms', '=',

joshmanhuwa's avatar
joshmanhuwa's avatar jlrdwLaravel
1

From normal DB query to Eloquent

Hi all, I would like to know which steps would you use to convert this into an Eloquent query please, do you know of any convertor? I have 2 problems in keeping this query this way: 1)I need pagination 2)I need to use ->where() as ->when($variable, function ($query) use ($variable) { $query->where('C1.sent', '=', $variable);

LaraBABA's avatar
LaraBABA's avatar sr57Eloquent
7

Unit test query builder

How do I create unit test for a method that makes query with query builder? Example: public function getStudents(){ $students = DB::table('students') ->join('contacts', 'students.id', '=', 'contacts.student_id') ->join('course_choice', 'students.id', '=', 'course_choice.student_id') ->join('courses', 'students.id', '=', 'course_choice.student_id') ->select('students

devmain's avatar
devmain's avatar Tray2Laravel
8

Missing rows in Query Builder vs SQL

I extracted a long and complex query from Query Builder using ->toSql() and ran it manually to get this https://imgur.com/a/N7kSLAl Then I used Laravel code to list the data in a browser, what happened was that: row3 was not present row5 and 6 was present (implying exact duplicates can returned) row100 was present Any idea why row 3 could have disappeared?

laracoft's avatar
laracoft's avatar laracoftEloquent
9

Sort Eloquent query by sub-relationship's latest()

I'm able to sort a query by one of its relationships foos : return new UserResource( User::with([ 'foos' => function ($q) { $q->latest(); }, 'foos.bar', ])->find($user_id) ); But actually, I need to sort it by it's subrelationship bar. Knowing that a foo can only have one bar. How can I modify my

devondahon's avatar
devondahon's avatar SilenceBri...Eloquent
1

How do I dump the result of a large query? (Symfony)

I'm using Symfony query builder. $queryBuilder = $this->em->createQueryBuilder() ->select('s, h, v') ->from(Shipment::class, 's') ->join('s.hops', 'h') ->join('h.vessel', 'v') ->where('s.finalPortConfirmed = :confirmed') ->setParameter('confirmed', false) ->getQue

Daniel1836's avatar
Daniel1836's avatar sr57Code Review
1

Query where users does not exist with specific IDs in table

Hello. I'm trying to make a query where I pull 10 users randomly, but they cannot exist with certain IDs in a table of lifestyle choices, they also cannot exist in a table of blocked users. The amount of IDs that should be filtered is unknown, so it should be some kind of array where multiple values are checked. So a user should be able to get shown random users, but without se

ddthatpost's avatar
ddthatpost's avatar sr57Code Review
1

Help with a join query

Hi All I'm a little stuck on a join query and was wondering if i'm barking up the wrong tree. In my app i have brands and products. Brands have options & sub options which can then be assigned to the product options & sub options which are related to the product (to save having to re create the options etc for each product). At the moment i've got it working and have as

ollie_123's avatar
ollie_123's avatar ollie_123Laravel
22

I want to check aganist array of values in a query

I have an array of values I want to check against in a query, so far this was how I looped it. But It doen't seem to work. $provider = User::query() ->where('user_type', 'admin') ->whereRelation('tag', function ($query) use ($followedTags) { $query->whereJsonContains('names', $followedTags[0]); for ($i = 1; $i &l

kachi_dk's avatar
kachi_dk's avatar rodrigo.pe...Laravel
3

How do I make a query to take conversations between two people chat system

Hi, I tried to make a chat system in laravel + vue one to one and I got a little stuck on the side when I have to take the conversations between two people I structured the database like this $table->bigIncrements('id'); $table->bigInteger('receiver')->unsignedBigInteger(); $table->bigInteger('sender')->unsignedBigInteger(); $

KikoLdasd's avatar
KikoLdasd's avatar iAmBsquareLaravel
3

Run DB query on each insert into a specific table

Hey! I need to somehow change a boolean value of a column inside a table (websites) based on every insert in another table (reviews). Both tables have relationship. On a new review inserted into the reviews table I need to change a boolean value in websites table (webscore_recalculation_needed to true) to which the review refers (reviews.website_id). Do you guys have any idea h

lat4732's avatar
lat4732's avatar LaralexLaravel
3

Rebuilding & optimizing this simple query

Hey! We have a lot of data in the database and I noticed this query is loading so slow. Could you please help me and convert this into a DB query builder code which is fully optimized? Reviews::with('website', 'user')->where('report_status', '!=', 3)->where('report_status', '!=', 1)->latest()->take(5)->get(), I tried with DB::table('reviews')

lat4732's avatar
lat4732's avatar LaralexLaravel
10

Which is better to use Eloquent ORM or Query Builder ?

Hi , everyone ! We have a big database and we will refactor our project to support RESTFul APIs , so we need to know which approxh is best . when we choice Eloquent ORM we know it have a performance issue , by the way with ORM we write a clenable and readable . but when we choice Query Builder we know it's a good choice for performance but we don't write a cleanable code . So W

codex.jo's avatar
codex.jo's avatar SilenceBri...Laravel
2

How to access model fields in ->leftJoin query?

Having an issue using left joins in queries involving multiple tables with fields of the same name. I'm using the Spatie laravel permission module (https://spatie.be/docs/laravel-permission/v5/introduction), and I am trying to sort my users by their roles. I'm also paginating the results, but the last line can be removed and replaced with a ->get() to remove pagination. My e

daveb2's avatar
daveb2's avatar daveb2Eloquent
2