Eloquent resource conditional relationship query

How can I solve/improve this issue. I have 3 models - Service, Landlord, Tenancy. All 3 have the following attributes - charge, letting_fee, re_letting_fee - with the them overwriting their respective parent value. For example, if a charge on a service is set to 10%, when setting the charge to 5% on the tenancy I want it to overwrite what is set on it's related landlord and ser

Jam0r's avatar
Jam0r's avatar frankincre...Eloquent
2

Complex query on a collection

I have an EmployeeController, and an AbsenceController When i load my index view showing all the absence records for an employee i use: $absences = Employee::find($employee_id)->absence()->get(); and pass it to my page. In addition I need to query this set of data for records that are between dates based on the records having a start and end date field (absence->start-

awfarral's avatar
awfarral's avatar Tray2Laravel
4

query in field

i want to make query in fields i used this but it's return fix value in resource Number::make('Name' , function () { return \App\Tag::query()->with('questions')->count() }),

Hala's avatar
Hala's avatar HalaNova
6

How can I write this eloquent query involving time zones and current datetime?

Hi I can't figure out how to write this scope query using Eloquent in Laravel. This is my latest attempt: public function scopeUpcoming($query) { return $query->where('start_date_time', '>', date('Y-m-d H:i:s')); } I have a table called 'Lessons' where the start_date_time is stored as the local timezone. Since Mysql doesn't allow the timezone to be stored

Lazlothemonkey's avatar
Lazlothemonkey's avatar Lazlothemo...Eloquent
5

Eloquent adds "is null" condition to a query and messes the results

Hello, I have the query that you can read bellow: Foo::where('some_status', 1) ->where(function ($query) { $query->where('other_status', 0) ->orWhere([ ['other_status', 2], [DB::raw('DATEDIFF(now(), other_status_updated_at) <= 2')] ]); })-&

cosminc's avatar
cosminc's avatar cosmincEloquent
4

PhpStorm, Laravel and CodeSniffer: query scope method not found?

I'm using local query scopes ( https://laravel.com/docs/6.x/eloquent#local-scopes ) in a functioning app. Recently added CodeSniffer to PhpStorm, and I get all these problems since the scope is defined in the model as "scopeNameOfTheScope", but it's used in the controller as "nameOfTheScope". See an example of the problem here: https://imgur.com/OMQqwp8 By t

mjsarfatti's avatar
mjsarfatti's avatar joshhurtGeneral
4

Logical mistake with eloquent query

I have below query code but i am not getting correct result. Is there any logical mistake in my code? $tasks = Task::where('deleted_at', null); $tasks = $tasks->where(function($q) use ($user_id,$company_id, $task_ids){ $q->where(function($q2) use ($user_id,$company_id, $task_ids) { $q2->where('created_by',$user_id)

engrlaravel's avatar
engrlaravel's avatar engrlarave...Eloquent
4

Applying a query string to a Route

So I have the following route configured that when I visit URL/employees/import it works great and the functionality does what I need it to do: Route::get('employees/import', [ 'as' => 'employees.import', 'uses' => 'EmployeesController@import' ]); Here is the EmployeesController class: class EmployeesController extends Controller { protected $employeeImporter; pu

Sema314's avatar
Sema314's avatar RamjithApLaravel
8

How to turn this into Query Builder?

This is my raw sql and i want to use this via query builder. Maybe anyone can help me? SELECT da.id as da_id, da.client_id, pai.amount from deposit_accounts da left join (select id,deposit_account_id, amount from posted_accrued_interests where date(created_at) = CURDATE() ) pai on da.id = pai.deposit_account_id where pai.id is null Thanks.

_NinjaDev's avatar
_NinjaDev's avatar pings12Laravel
1

Query string for a route

Hi all, So the way that I currently have things, is that I'm calling it as /test/import Route::get('test/import', [ 'as' => 'test.import', 'uses' => 'TestController@import' ]); Which pulls in the correct import method inside TestController.. Here is what I'm trying to achieve: How can I make it test/import/?mode=test1 or test/import/?mode=test2 that calls on different met

Sema314's avatar
Sema314's avatar MichalOrav...Laravel
10

Modify Sql query to Eloquent query

I need a help to modify my sql query to eloquent query, i fetch data from two table here products and Loadings SELECT products.id,products.name as name, ( (products.open_stock) +(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and MONTH(stocks.Indate) <= MONTH(CURRENT_DATE())-1) - (select ifnull(sum(loadings.qty),0) fr

melx's avatar
melx's avatar willvincen...Eloquent
14

Eloquent nested query

I have 2 modules channels and users I want to return the users count when selecting a specific channel, so I made an eloquent relationship in channels model users() but the performance is a bit bad so I made a lot of googling and I ended up with using nested query. so how can I make something like this in laravel // Model Relationship public function users() { ret

AhmedNaser's avatar
AhmedNaser's avatar AhmedNaserLaravel
4

Get ID Array and use in second Query

I'm getting myself in a corner If anyone could assist please, I need to get an array of Area id's then use that array to show all the related properties of those areas. Below is what i have so far. $areaass = AreaAss::where('areaid', $AreaIds) ->select('associd') ->get(); $property = LeaseProperty::whereIn('areaid', [$areaass]) // $p

Alidnet's avatar
Alidnet's avatar AlidnetEloquent
2

How to merge three query in one

I have three query, and i want help to merge to have one, and order by Indate and Outdate as date $item_load_list_cur_month=DB::table('products') ->select('loadings.qty','loadings.Outdate','products.name') ->join('loadings', 'loadings.pid' ,'=','products.id') ->whereYear('loadings.Outdate', Carbon::now()->year) ->whereMon

melx's avatar
melx's avatar bugsyshaLaravel
28

How to add a collection to another returned query result?

Here I have this eloquent query: $supplierCoverages = SupplierCoverage::whereIn('supplier_id', $supplierListing) ->leftJoin('areas', 'supplier_coverages.area_id', '=', 'areas.id') ->leftJoin('regions', 'areas.region_id', '=', 'regions.id') ->get() ->sortBy('name')

Haziqasyraff's avatar
Haziqasyraff's avatar bobbybouwm...Eloquent
1

Generating Test with Mocking Query

I have the main feature request test partly written. On my users.index page it renders 3 different Livewire components that are paginated user datatables. One for active, one for inactive, and unactivated. I'm wanting to test each individual Livewire component. The problem I'm having is that I was trying to run a query inside of the test and make sure I return the right data.

jrdavidson's avatar
jrdavidson's avatar xtremer360Testing
9

Laravel Many to Many Polymorphic Query

Suppose we have many to many polymorphic relationship same like Laravel documentation: posts id - integer name - string videos id - integer name - string tags id - integer name - string taggables tag_id - integer taggable_id - integer taggable_type - string The main question

zizi_ove's avatar
zizi_ove's avatar zizi_oveLaravel
2

How to update Model with Sub Query results

So, in the Tweety project from "Laravel from Scratch" series we have a subQuery join to populate the count of likes and dislikes. This is then used by withLikes() in the query for Users' timeline: https://github.com/igorbenic/Tweety/blob/master/app/User.php#L54-L58 The subquery is here https://github.com/igorbenic/Tweety/blob/master/app/Likable.php#L9-L17 When I want

ibenic's avatar
ibenic's avatar pilatEloquent
2

Livewire not accepting global query scope data into Component's public property

Okay, here's the deal. I'm quite new to Livewire and Laravel in general, so I was doing "homework" on assignment from "Laravel (6) from scratch" series - building Twitter clone. My post might be quite long because of my inexperience - so most of it should be for sanity check reasons, hope you can stay tuned. Here goes: one of the things I wanted to do is to

tal3nce's avatar
tal3nce's avatar tal3nceLivewire
1

DB insert doesn't work but raw query in MySQL CLI does

I have the following query insert into ports (id, un_country, un_location, un_name, un_subdivision, un_status, un_function, un_remarks, coordinate, alternate_names) values ('1', 'AD', 'ALV', 'Andorra la Vella', '', 'AI', '--34-6--', '601', '0x000000000101000000F6285C8FC2F5F43F6666666666264540', '') It easily works using MySQL CLI or something like Phpstorm db console but once

Nazlobudnya's avatar
Nazlobudnya's avatar jlrdwLaravel
4

Unable to return counts of 0 (zero) with query builder

Hi there! Hopefully this is an easy one for somebody more eloquent-savvy than myself. With the following query, I am returning info about a user and their team, as well as a count of how many times each particular "stat_meta" type appears in the "basketball_game_events" table. However, when that count returns 0, it just doesn't return anything for that row.

hunterhawley's avatar
hunterhawley's avatar jlrdwEloquent
4

Laravel Eloquent query OR problem

I have a query $tasks = Task::where('deleted_at', null)->where('company_id',$company_id); $tasks = $tasks->where('created_by',$user_id); $tasks = $tasks->orWhereIn('id',$task_ids); It generate below query SELECT * FROM `tasks` WHERE `deleted_at` IS NULL AND `company_id` = 25 AND `created_by` = 20 OR `id` IN(112,...215) ORDER BY `id` DESC Now Id 112 is deleted but s

engrlaravel's avatar
engrlaravel's avatar MichalOrav...Eloquent
11

chunking huge query with withcount and orderby

i have a pretty heavy query that hits the memory limit and return 500 error here is the query : $collection = User::with('city') ->withCount('userReferral') ->orderByDesc('user_referral_count') ->get(); $data = $collection->where('username', $userName); this is for a ranking that shows the user rank on the rankin

farshadf's avatar
farshadf's avatar farshadfLaravel
7

Eloquent - Some relationships missing when using query ->with()

I have the following: Garment.php: public function properties(){ return $this->belongsToMany('App\Property'); } Property.php: public function garments(){ return $this->belongsToMany('App\Garment'); } Following laravel convention for table names : Garments, Properties. Fks: garment_id, property_id . They have a many-to-many relationship. I'm Try

rubu710's avatar
rubu710's avatar rubu710Eloquent
8

For select query which is better eloquent query or db query? and why?

For select query which is better eloquent query or db query? and why?

kaushal's avatar
kaushal's avatar jlrdwLaravel
2

Query working on local, failing on production

So I'm pretty confused because I have a query that passed all tests on the local environment but on production it fails. This is the query. if ($request->date === null) { $direction[0] = $request->direction ? 'max(date)' : 'min(date)'; $direction[1] = $request->direction ? 'DESC' : 'ASC'; $dataRevToday = Revision::select((DB::raw($direction[0] . ' as "

cuartas15's avatar
cuartas15's avatar cuartas15Eloquent
1

Ajax request is reloading page and adding query strings to uri

I have created a review system, which is working and I have Vue components that make ajax post requests to my review controller. Everything is working, except whenever the form is submitted, it makes a full post request to the site, and reloads the page. It also adds a query string to the uri "body=test" etc. What am I doing wrong here? You can see the website here, b

cwray-tech's avatar
cwray-tech's avatar cwray-techLaravel
5

Same query in Digital Ocean Managed MySQL DB more than doubly slow than on droplet.

Hey all! May be a dumb question, but I'd rather ask. I currently have my DB on a separate droplet in DO. The Spec is: 2 GB Memory, 2 vCPUs, 3 TB, 60 GB DISK, $15/mo I'm in the process of migrating to a Managed DB and have opted for the cheapest spec to start: 1 GB Memory, 1 vCPU, 10 GB DISK, $15/mo For the most part I don't see any performance hits however I have one page (Dash

ralphmorris's avatar
ralphmorris's avatar mrsparoServers
3

Laravel eloquent Query problem

My eloquent query generates below sql query SELECT * FROM `tasks` WHERE `deleted_at` IS NULL AND `company_id` = 25 AND `created_by` = 20 OR `id` IN(112,...215) ORDER BY `id` DESC Query is here $tasks = Task::where('deleted_at', null)->where('company_id',$company_id); $tasks = $tasks->where('created_by',$user_id); $tasks = $tasks->orWhereIn('id',$task_ids); Now the

engrlaravel's avatar
engrlaravel's avatar SnapeyEloquent
2

Query builder Avg method, problem with float numbers

Trying to create a 5-star rating system in Laravel. a star rate is a float number in the range [1 to 5] and it stores successfully in the database. migration file for rates table in database. ( I've set data type of star column as float) public function up() { Schema::create('rates', function (Blueprint $table) { $table->id(); $table-&

zizi_ove's avatar
zizi_ove's avatar zizi_oveLaravel
3

Query depends on multi optional parameter

I have two optional parameter and need to query them with condition how could be that ? i used this ,but there is no relation return back with it public function show(Request $request,$id, $languages= null,$destinations=null ) { $planner=Planner::findOrFail($id); if($languages){ $planner->with('language'); } if($destinations){ $planner-&g

Hala's avatar
Hala's avatar MichalOrav...Laravel
3

How to perform a query search of all "clients" from a Builder instance of "products"

So, i have a Builder instance of "products" with Product::orderBy('purchase_date', 'desc') , and each of my products have a 'client_id' field, now i just want to get all products where the client name is like something, how can i do it? That is the (ugly) way i implemented it, is this correct or there is a better way to do it? // Here i get all products $products =

JoaoHamerski's avatar
JoaoHamerski's avatar JoaoHamers...Laravel
2

Query with special condition like 2%2 =0

When user buy two of the item will get a discount. Now the problem is when the user buy four qty of the product i cant match it out. Therefore i need to do some math in query. Like quantity%database.quantity == 0. How can i do that in my query $promotionID = Promotion::where('product_id',$product) ->whereRaw('promotion_quantity%'.$quantity.'=0')

tigerjun's avatar
tigerjun's avatar tigerjunLaravel
1

How to Insert Auth::user()->id in sql query

What can i do to store Auth::user()->id user_id on this sql query, public function saveLoadingsData() // Validate for a valid Post Request if (isset($_POST['orderNumber']) && isset($_POST['Truck']) && isset($_POST['receiptNumber']) && isset($_POST['items'])) { // {"orderNumber":"CRS1104200001","agentI

melx's avatar
melx's avatar SnapeyLaravel
1

Query Builder and sql row_number

Hello, guys! $query = DB::table(DB::raw("({$subQuery->toSql()}) AS sub")) ->mergeBindings($subQuery->getQuery()) ->select(DB::raw(' sub.*, products.id AS p_id')) ->addSelect(DB::raw('ROW_NUMBER() OVER (PARTITION BY p_code ORDER BY p_group DESC) AS row_num')) })->j

Rem000's avatar
Rem000's avatar Rem000Eloquent
0

Improvement of complex Eloquent Query

I have some code which queries a database on which i don't have any control (i can't change the structure or anything). In this database there is table created for every campaign in the campaigns table. To hold the history. I need to query this history tables to get the results and monitor some employees. The following code works, but i don't like it. Do you guys have any impro

s.spaan's avatar
s.spaan's avatar s.spaanEloquent
2

Query into where clause ?

Hi, anyone help me please, i have this query: $allUser = DB::table('users')->join('anything','example.id,'=','example.id') ->select('example.name') ->where([ ['example.id', $hereIwantAnotherValueOfTheQuery],['example.exm'] ])->get(); but : $hereIwantanotherValueOfTheQuery is another query that i write before, i want know if there are some method to add a query(

MCLTano's avatar
MCLTano's avatar MCLTanoEloquent
1

Laravel relationship count query

Hi , I have tables with below table structure UserTest Model with columns names (user_test_id(primary), test_id, marks_scored,status) Question model with column names (question_id (primary), test_id, question_text,correct_option) UserAnswer model with column names (user_answer_id (primary) , user_test_id (foreign), user_answer, question_id) i have three requirement to get tota

Deekshith's avatar
Deekshith's avatar DeekshithGeneral
1

Duplicate User Query in Policy

I have a policy that seems to be duplicating the authorized user query. I'm seeing this query: select * from `users` where `id` = 1 limit 1 /vendor/laravel/framework/src/Illuminate/Auth/EloquentUserProvider.php:52 And also this one, which is slightly different in my policy: select * from `users` where `users`.`id` = 1 limit 1 /app/Policies/ProjectPolicy.php:20 Here's what the

ajgagnon's avatar
ajgagnon's avatar ajgagnonLaravel
4

how to make a multiple filter query?

i have this code to filter public function viewType(Request $request, ReportViewAll $reportview){ $reportview = $reportview->newQuery(); if ($request->has('program')) { if($request->input('program') == 'reportall'){ $reportview; } elseif($request->input('program') == 'reportactive'){

rickyreza12's avatar
rickyreza12's avatar kalemdziev...Laravel
1

Why don't my query eager load?

This is my query $car = Car::query() ->orderby('id', 'desc') ) ->with('User') I expect Laravel would eager load the user object here? When I call $car->User->age I get multiple queries. What is going wrong here?

soyiso3875's avatar
soyiso3875's avatar bobbybouwm...General
1

Is it possible using model methods to make a query?

For example, i have a method in my model to check if the Product is paid. public function isPaid() { return $this->getRemainingValue() value != null); } How can i iterate over all registers and get only the models that "is paid" but using this method, without writing a new query. Something like iterate over all registers and return all models that are true using

JoaoHamerski's avatar
JoaoHamerski's avatar jlrdwLaravel
5

How to Query Spatie Role-Permission

In my Laravel-5.8 Spatie Project. I want to query the id and name of user in users table where Spatie User Role is "Super Admin". How do I do this? Thank you.

noblemfd's avatar
noblemfd's avatar ProkosaLaravel
5

Make the query builder interpret a value as a column name

(Context: query scope where $value is either true or false) I'd like to do this: return $query ->withCount('persons') ->having('persons_count', $value ? '>=' : '<', 'allowed_submissions'); But have to use raw because 'allowed_submissions' is interpreted as a value return $query ->withCount('persons') ->havingRaw('persons_count ' . ($value ? '&

mabdullahsari's avatar
mabdullahsari's avatar kalemdziev...Eloquent
1

Problem with getting data from query

I have two tables field_value id field_id value label sort user_interests user_id field_id value_id I am trying to write a query where I will get user with id 1 and have field_id 2 and to be able to echo in my blade value_id 4 and 5 but not to echo those ids but to echo value of 'label' column that corresponds to value_id form user_interests table in this case 4,

dr24's avatar
dr24's avatar dr24Laravel
0

Mysql Raw query with groupBy in laravel

Is there any better way of using Eloquent or Query builder $PurchasesdAmount = DB::select( 'SELECT pp.supplier_id,SUM(pp.amount)AS PaidAmount,pp.id, (SELECT NAME FROM suppliers sa WHERE sa.id = pp.supplier_id) AS Supplier FROM purchases_payments pp Group BY pp.supplier_id' ); return response()->json(['d

UsmanBasharmal's avatar
UsmanBasharmal's avatar UsmanBasha...Laravel
0

Route parameter in the query string

Hi there I have a route which sometimes accepts parameters in the query string, but the problem is whenever I add a parameter to the query string I got 404 response code for my api. Route::prefix('users')->group(function () { Route::put('profile', 'UserController@updateProfile')->middleware(['ability:owner,edit-users']); Route::get('profile', 'User

AhmedNaser's avatar
AhmedNaser's avatar SaneeshLaravel
1

Query to Eloquent

Hi, I have this query and want to convert it to Eloquent. Can someone help me? \DB::select('select * FROM cups WHERE id in ( select cup_id from products WHERE category_id IN (12, 29, 54) GROUP by cup_id )');

nevakil's avatar
nevakil's avatar piljac1General
2

Product filter query return me 500 why.

Query filter not working i just use filters but when i set query without filter like below it works but in second case i add the filter but its throw 500 why please help. This works in first case return all products $products = Product::where(function($query){ })->get(); echo json_encode($products); But when i try to enter filter by price free or paid it retu

tehseen's avatar
tehseen's avatar DiogoGomesEloquent
3

Get custom relationship data with Eloquent query builder

I have this query : FHINotification::with(['log']) ->where('user', $user->getSAMAccountName()) ->whereNull('read_at') ->get(); "Log" model has a function to get custom data (more human readable than data in db) public function getData(): array { Game::setWithoutStandardEdition(true); $ldapService = App::make('App\Services\LdapService')

Youtaka's avatar
Youtaka's avatar YoutakaEloquent
2