chain where stament with varible and use it on query builder

HI, guys. im trying to set a where stament into variable to set in query builder, but this is posible? private function generateQueryAnnualReport(bool $all_entities, $period, $entity_id) { $where_statement = null; // Determinate which use if ($all_entities) { $where_statement = `where([ ['entity_documentation.documentat

hcastillo's avatar
hcastillo's avatar hcastilloLaravel
2

Paginate Query Builder request

Can I paginate a Query Builder request like below ? Route::get('users/duplicates/names', fn () => DB::connection('myconn')->select( 'SELECT LOWER(unaccent(first_name)) AS fn, LOWER(unaccent(last_name)) AS ln, COUNT(*) as count, ARRAY_AGG(id) AS ids FROM myschema.users WHERE (last_name = \'\') IS FALSE GROUP BY LOWER(

devondahon's avatar
devondahon's avatar tykusGeneral
5

Filter query relation

Hi, I have venue's with events. The events have a category. I want to show a page with all venues with their events. But I only want to show the veneus with the category with id '2'. Code below shows the venues containing an event with category '2'. How can I show only the events that are category '2' because now I see all events. $venues = Venue::whereHas('event', function (Bu

EricZwart's avatar
EricZwart's avatar EricZwartEloquent
4

Laravel query builder with multiple sub queries

I have these two queries, $data = Room::whereNotIN('id', function($query) use($request) { $query->select('room_id') ->from('locks') ->where('created_at', '>', Carbon::now()->subMinutes(15)); }) ->get(); $data = Room::whereNotIN('id', function($query) use($request) { $query->select('room_id') ->from('bookings')

Reaper's avatar
Reaper's avatar SilenceBri...Eloquent
1

Laravel Eloquent query

Hi, Im trying to make an statment to make my calculation inside in a query Service::query()->where('services.customer_id', '=',$this->custname)->join('payment_invoices', 'payment_invoices.customer_id', '=', 'services.customer_id') if (payment_invoices::where('id', $this->custname)->exists()) { ->sum('payment_invoices.total_cost'); } else { ->sum('service

Chris1989's avatar
Chris1989's avatar MohamedTam...Eloquent
4

Convert Query Builder to Eloquent

Hi, could someone kindly assist me to convert the following query to Eloquent, Kindly? $availableRooms = DB::select("SELECT * FROM rooms WHERE id NOT IN (SELECT room_id FROM bookings WHERE '$arrival' BETWEEN arrival AND departure)");

Brammah's avatar
Brammah's avatar SinnbeckLaravel
14

How can I use $this for the relationship object inside a query?

Hey guys! Have a look at this query $company->emailInvitations() ->where('email', $request->email) ->whereBetween('created_at', [$this->created_at->subMonth(), now()]) ->exists() How can I use $this for the relationship object?

lat4732's avatar
lat4732's avatar LaralexLaravel
11

Parent id into select query

I need Category::find(78)->slug as 'parent_slug' into query, but how to do it? Category::where('parent_id', 78) ->select('name', 'slug', DB:raw(Category::find(78)->slug as 'parent_slug')) ->get();

Ajvanho's avatar
Ajvanho's avatar MichalOrav...Laravel
1

how to do a raw query within a scope function of a model?

I have a raw query that I would like to use as a scope function so that I may simply call the function to get all the data I need instead of writing out all the inner joins using the model. something like this: public function scopeGetMyData(){ return $query->raw(" SELECT * FROM table LEFT JOIN table2 on table2.id = table.id"); } I know I could do something lik

PersonalHomePage's avatar
PersonalHomePage's avatar PersonalHo...Eloquent
4

Query Categories with Nth Child Category

I have a the below category model. How do i perform a query that first returns the parent category and the can subsequently query for its child category irrespective of the depth. With my existing model i can query a child category using the childrenCategories on blade. I would like to return this information via an api get. That is to say something like this: How i think the c

ComputerMaverick's avatar
ComputerMaverick's avatar jlrdwEloquent
3

how to make loading page during the query data process using ajax.

Hello everyone, I have a question. About how can I make the loading page when query data with Ajax. So when the data has not been obtained, a loading page will appear. and when the data is displayed, the data will appear.

adnanerlansyah403's avatar
adnanerlansyah403's avatar SnapeyJavaScript
2

How to query and compare past week and current week record?

I want to query my past week record compare to my current week record. My database store the data by day. To get current week record i could simply query it with startOfWeek and endOfWeek to calculate the total_count. For example, i want to get the record from past week vs current week. I have a column person_count which i will sum it for this week compare to sum of person_coun

Crazylife's avatar
Crazylife's avatar CrazylifeEloquent
7

Using functions inside the Eloquent query in a clean way, help

Hello, Help me clean up the code? Need to call the function which has to be written separately, not in the Eloquent query. I need to make a transform on the pagination object (apparently it can be done with through() method) in order to get the preview text for my list of Posts with some clean-up to the source text. What I want is to put the function name in the query, not the

Mike P's avatar
Mike P's avatar Mike PLaravel
11

Better use of Query Builder

I'm using Livewire, I want to filter the images,And I want to display the images in two ways based on the radio button,One based on user uploaded images and the other based on category public function render() { $images = Gallery::query() ->when($this->selectedStatus, function ($query) { return $query->where('category', $this

shimana's avatar
shimana's avatar shimanaLaravel
2

Help with a query to conver in eloquent with relationship

I'm using Laravel 8 I have many relationships in the models, I want to create this query in Eloquent, could somebody help me? SELECT D.name AS Dimensiones, SUM(A.value) AS Total_Dimensiones FROM answer_application ANP INNER JOIN answers A ON ANP.answer_id = A.id INNER JOIN questions Q ON Q.id = A.question_id INNER JOIN dimensions D ON D.id = Q.dimension_id GROUP BY D.name; This

Josadec's avatar
Josadec's avatar JosadecEloquent
4

Query don't return null if value don't exist

I have a strange problem getting data from array. Currently I'm developing an auto translation system for some Excel file that I have I have My database table "dictionaries" and the columns are: | it-IT | en-GB | de-DE | other languages.. I import an excel file and I group it by a reference. For each group I need to find the text translation in the db, in this case: I

forna91fi's avatar
forna91fi's avatar forna91fiEloquent
0

Complex eloquent query doesn't behave properly

Hello, here is the query: Topic::with( [ 'user', 'topicCategory', 'tagLanguage', 'tagEnvironment' ] ) ->when( $selectedLanguageTagIds, fn( $query ) => $query->whereHas( 'tagLanguage', fn( $q ) => $q->whereIn( 'id', $selectedLanguageTagIds ) ) ) ->when( $selectedEnvironmentTagIds, fn( $query ) =>

brakkar's avatar
brakkar's avatar brakkarLaravel
2

filter query for multiple checkboxes

I need to filter users depending on a market category ( the user could signup and add multiple categories in his profile ) Models USER->MarketCategoryTransactions->MarketCategory Now I need to show users filtered on this category. blade <div class="sidebar-widget"> <h3>Market Segment Speciality</h3>

FounderStartup's avatar
FounderStartup's avatar jhgjhgjhLaravel
5

make a query in controller

Hello , i am trying to pass all the dissertations where exists on my DB from the specific Auth::user, connected with internal_examiner_id which is user's id. in this line 'dissertations' =>Dissertation::all()->where('internal_examiner_id','12'), I am trying to make the number 12 dynamic, i want to use the Auth::user's id, ive tried Auth::user()->id,tried to make a vari

Inpanic4's avatar
Inpanic4's avatar StavrosG4Code Review
2

query is fast | loading results in page VERY slow

Newish to Laravel. Simple query: $participants = DB::table('users') ->where('course_serial', $serial) ->where('t_status', '<>', '') ->orderBy('Id', 'asc') ->get(['Id','first_name','last_name','email', 'phone','serial', 't_status']); Query takes "0.010648012161255" - yet loading the 22 results the query returns in a blade view with @foreach takes +30

X chris X's avatar
X chris X's avatar X chris XCode Review
11

pass same query to multiple variables

i have a query like below $confirmedusers = Orders::with(['userdetail.useridcards'])->join('user_registered_courses','user_registered_courses.user_id','=','orders.user_id')->join('users','users.user_id','=','user_registered_courses.user_id')->where('user_registered_courses.course_id',$course_id)->whereNotNull('user_registered_courses.registration_number')->where(

Deekshith's avatar
Deekshith's avatar DeekshithLaravel
3

Build Search query by reference ID in a table

I might be using wrong terminology/words here. I have 2 tables Orders | id | order_id | customer_id| | 1 | RD34234234 | 3 | | 2 | CF43345376 | 4 | Payments | id | order_id | pay_id | | 1 | 1 | pi_jkfshjk32948sdf | | 2 | 2 | pi_234lkj3483898ad | I have a relationship between them so in my admin pa

eaglehdr's avatar
eaglehdr's avatar eaglehdrEloquent
6

Eloquent query by first letter only

Hi All! I need to query my db records by a starting first letter or number only, nothing more. In my SearchController, I have the following, but it returns all records containing the given term in the title. Is there a way to do this? Thanks. // SearchController.php public function browse($term) { $items = Item::query() ->with(['image']) ->where('titl

dink's avatar
dink's avatar dinkEloquent
2

Multiple DB Ray query

select top 400 * from Com_Person_M where not exists (select * from old_db_migration_status where Com_Person_M.PersSequ = old_db_migration_status.old_record_id and type = 'com_person_m') How to make raw query for the above Com_Person_M is sqlsrv connection & old_db_migration_status is mysql connection. Please help

kshitizmittal's avatar
kshitizmittal's avatar SinnbeckLaravel
1

Cleanly query role in RBAC from set of permissions using eloquent?

Given a Role-Based Access Control (RBAC) design (see figure), I'm wondering if there is a clean way to query for the existence of a role (parent table) that exactly matches a specific set of role permissions (child table)? I can certainly do it using a combination of eloquent and application logic in PHP, but I'm curious if there is a clean way to do it with just Eloquent if I

anjama's avatar
anjama's avatar anjamaEloquent
3

SQLSTATE[70100]: \Unknown error>>: 1317 Query execution was interrupted

I am trying to execute this query but giving me this error: I will change a column from not unique to Unique. partner_id is a foreign key. N:B: I am working in Laravel 5.3 class ChangePartnerToPartnerFinancialInformationsTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::table('partner_f

nafeeur10's avatar
nafeeur10's avatar jlrdwLaravel
3

Help with Query

I have a db of following tables (with example data) years { "id": 1, "name": 2011, "created_at": "2022-03-09 13:18:47", "updated_at": "2022-03-09 13:18:47" } makes { "id": 1, "name": "Toyota", "created_at": "2022-03-09 13:19:09", "

ntn0de's avatar
ntn0de's avatar Tray2Eloquent
3

hi guys i try bring extra_subcategories on subcategories i think that if i put wire click and fuction do the query its easy to play but not working , the reason that propably not working are when i selected 2 subcategories not update array

My livewire code public $SelectedCat ; public $categories= []; public $subcategories= []; public $extra_subcategories= []; public $SelectedSubCat = []; public $SelectedExtraSubCat = []; public $SelectedTag = []; public $SelectedAddress = []; public $search=""; public $searchTag=""; public $searchAddress=""; public $location=""; public $

Constantine_92's avatar
Constantine_92's avatar Constantin...Livewire
0

Is my query correct when using pivot table?

Assuming both models ItemsList and Items are set correctly with belongsToMany , and the pivot table is items_to_list, I need to get the list of items with selected category and where the items are banned in the pivot table Does this query seem to be correct $lists = ItemsList::whereIn('id', $array_of_ids) ->with('items') ->withCount(['items' => function ($query

cooperino's avatar
cooperino's avatar cooperinoEloquent
4

Laravel Query builder puts whitespace on my string

Hi, I'm new on this forum. I have a problem with a line of the query: whereBetween("CdrRaw.datetime", [$DataI,$DataF]) This attempt threw an error and the query debug displays: where 'CdrRaw'.'datetime' between ? and ? So I tried to make it Raw with ->whereRaw("CdrRaw.datetime between $DataI AND $DataF") And here I could see the problem: the query builde

Nicolath3cat's avatar
Nicolath3cat's avatar SinnbeckEloquent
9

Eloquent Query within foreach loop

Hey Folks, Back again with another question I am hoping someone might have an idea for regarding the app that I am working on. In this educational assessment application, I have assessments which have a one-to-many relationship with Competencies (similar to a Blog Post Category) and a many-to-many relationship with Contexts (similar to a Blog Tag). I am trying to create a repor

inquisitvewaffle's avatar
inquisitvewaffle's avatar Tray2Code Review
1

Soft Delete And "No query results for model" issue in queue

I dispatch a model with delay 1 hour, In the middle (after 30minutes) i soft delete the model before it get handle by queue system hence i will got ""No query results for model", My question is, How can i tell laravel to retrive models even though they got soft deleted in job class?

untymage's avatar
untymage's avatar tykusLaravel
1

Date And Multiple Field Filter Through SPATIE query builder for post request

I go through 'SPATIE query builder' documentation and find they cannot describe properly how to post methods implemented. I need to filter data based on multiple parameters including the data add date filter, and I am using the POST method, How I can achieve this for 'SPATIE query builder'. I can Filter 1 field by writing below code $data = QueryBuilder::for(EmpData::where('emp

aGreenCoder's avatar
aGreenCoder's avatar DevWebTKMix
7

Have enableQueryLog show me where the query exists.

So I am using enableQueryLog and it is showing me the queries that are being executed and how long they take. However I am wondering if there is a way to see the class and line number that executes said query. How do I see this information. Side note, I am using the illuminate capsule/manager to grab this info so I can use querybuilder and eloquent in a non Laravel application

shawndibble's avatar
shawndibble's avatar SinnbeckEloquent
4

Laravel SQL Query Problem

I have this SQL Query Statement below SELECT DISTINCT parent_category.id, parent_category.description FROM category AS sub_category INNER JOIN category AS parent_category ON (sub_category.parent_category_id = parent_category.id) WHERE sub_category.client_id = '19' AND sub_category.id IN (SELECT category_id FROM `after_hours_yesterday_category` WHERE cost_in_hours + cost_out_of_

monstajamss's avatar
monstajamss's avatar monstajams...Laravel
2

Query Optimization

I run this query it't take 7-8 second there are more than 1 million data in cars and cars stocks SELECT cars.id, cars.name, car_stocks.created_at FROM carsLEFT JOIN car_stocks ON car_stocks.car_id = cars.id and date(car_stocks.created_at) = '2022-01-18' Please suggest if you have a more optimized/less time consuming query

VinayPrajapati's avatar
VinayPrajapati's avatar Tray2Laravel
10

Use a condition on a relationship query

I have the following eloquent query $approved = InvoicesSprout::with(['payment' => function ($query) { $query->select('id', 'pay_method') ->where('pay_method' , 0) #Important where condition - A1 ->orWhere('pay_method' , 2); }] ) ->with(['client' => function

Victor Alfonso's avatar
Victor Alfonso's avatar SinnbeckEloquent
3

Get specific values from SQL Query

I am trying to get the values of koh, kih etc specifically in this SQL Query below $afterhours = $this->electricityConnections->select(\DB::raw('SUM(kwh_used_out_of_hours) as koh'), (\DB::raw('SUM(kwh_used_in_hours) as kih')), (\DB::raw('SUM(cost_out_of_hours) as coh')), (\DB::raw('SUM(cost_in_hours) as cih'))) ->from('after_hours_yesterday_ca

monstajamss's avatar
monstajamss's avatar monstajams...Laravel
2

Is there a way to make this query faster?

This is the same query from my previous posts: DB::table('items')->select('items_to_list.user_id', 'items.item_code') ->distinct('items_to_list.user_id') ->leftJoin('items_to_list','user_id', '=', 'items.id') ->where('items_to_list.status', '=', 0) ->where('items.deleted', '=', 0) ->get() If items_to_list table has 100,000 rows, this query takes about 0.8 sec

cooperino's avatar
cooperino's avatar cooperinoCode Review
10

Separate query builder results to arrays

If I have a query that gives me collection with array of items (DB::table('items')->select('item_id', 'item_code')) Illuminate\Support\Collection {#2992 #items: array:2 [ 0 => {#2993 +"item_id": 11 +"item_code": "A1B2C3" } 1 => {#2994 +"item_id": 11343 +"item_code": "A1B2C4&quo

cooperino's avatar
cooperino's avatar SinnbeckCode Review
1

Where is my mistake in the conversion from SQL to Query Builder?

I am trying to convert an SQL query to Query Builder. The SQL query: SELECT DISTINCT(items_to_list.user_id), items.item_code FROM items LEFT JOIN items_to_list ON items_to_list.user_id = users.id WHERE items_to_list.list_id IN (10,20); My Query Builder attempt: DB::table('items')->select('items_to_list.user_id', 'items.item_code')->distinct() ->leftJoin('items_to_list

cooperino's avatar
cooperino's avatar cooperinoCode Review
2

How to skip data when doing query if left join is exists?

How to skip data when doing query if left join is exists? I'm trying to get data. First I get the in and then join it with out data. How to skip/remove data from query if the out data from left join is exists? I don't want my query to get the data if it the out data is exists. Example Data: | id | in_out | work_hour_type | work_hour_start | work_hour_end | attendance_time |

catto's avatar
catto's avatar SnapeyLaravel
1

Duplicated query because of json-ld

Hey! I'm inserting json-ld into my pages but I faced a problem. On the page where I show reviews for a particular website, those same reviews are taken from an AJAX call, which takes HTML formatted code directly, which puts it in a div. I actually need these reviews details in the json-ld script tag. But now, in order to achieve what I want, I have to make a new (same) query to

lat4732's avatar
lat4732's avatar LaralexLaravel
8

How to I get query strings of previous URL?

I am trying to get a query strings from previous URL in Laravel. My app is similar to Formspree where users can submit a form using and it get send to my website through POST method. Here is a form hosted on external website <!-- www.user-website/?ref=53saf2 --> <form action="https://localhost:8080/s/FORM_KEY" method="POST"> <input type=&qu

navneet's avatar
navneet's avatar nano7Code Review
8

Bad performance with doesntHave query

For simplicity, I'm gonna use different table names than my current project. I have tables t1, t2 with many to many relationships and pivot table t1_t2 which have t1_id and t2_id columns, and at the beginning, I start using doesntHave query like this. T1::doesntHave('t2')->get(); After a while, the tables become bigger and bigger with millions of records and the query took

moelkomy's avatar
moelkomy's avatar Tray2Eloquent
13

.htaccess redirect removing all query strings

I have a url like this; example.com/foo.htm?name=kdu (any query string) I want to redirect to this page; example.com/blog/bar - completely removing anything after the question mark Any idea, please?

2sweetworld's avatar
2sweetworld's avatar 2sweetworl...Code Review
4

nested query issue

I need '$similarlistings' with city = $listing->city. Its a simple query but I may be missing some basics here :) controller public function ListingDetails($id) { $listing = Listing::findOrFail($id); Listing::find($id)->increment('views'); $responses = ListingResponse::where('listing_id', $id)->paginate(10); $totalresponses

FounderStartup's avatar
FounderStartup's avatar SinnbeckLaravel
15

eloquent query by date

Here is the image: https://imgur.com/Uya8P70 Basically, let's say, I have created: 2 posts on 1 Feb, 4 posts on 2 Feb, None on 3 Feb, 3 posts on 4 Feb, and so on... I want to get query in array something like: [ [2022-02-01] => [ ["id" =>1, "title" => "Post 1" ], ["id" =>2, "title"

krunal05's avatar
krunal05's avatar krunal05Laravel
4

Running raw db query with mysql's own prepare and execute statements

Hi all, can anyone point me to right direction. I have some raw mysql query that counts table rows from all databases (I have also other queries in similar syntax that fetches colum values). I have problem with the last portion of query (prepare and execute), if I use raw and statement it return error. Here is raw query in question: Select concat( 'select * from (', g

dimitri's avatar
dimitri's avatar dimitriEloquent
0

How to query records count based on days?

I have a table of Forms where people can sign up and their details are stored in the table. I am trying to display the statistics on how many number of people sign up on the particular day. For instance, my blade view should look like this: Date Number of Sign Up 25 Feb 2022 1 24 Feb 2022 2 23 Feb 2022

CookieMonster's avatar
CookieMonster's avatar SinnbeckLaravel
30