Converting SQl Query to laravel

I am trying to convert SQLquery to Laravel query . Database used is PostgreSQL Laravel $c_list = DB::table('cities') ->select('id as city_id', 'name as lang_name', 'ascii_name as city_name', 'iso2', DB::raw("(CASE WHEN iso2 = 'DE' THEN 'Germany' WHEN iso2 = 'CH' THEN 'Switzerland' ELSE 'India' END) AS codeCity")) ->whereIn('iso2', array

satheeshkumarj's avatar
satheeshkumarj's avatar richardhul...Code Review
1

PHP Fatal error: Uncaught TypeError: Symfony\\Component\\HttpFoundation\\Request::createRequestFromFactory(): Argument #1 ($query) must be of type array, null given

I'm facing this error in laravel 9 project (recently upgraded from 8) I have upgraded it by manual respectively for all impact changes. it is throwing an error 500 and can not understand what does it need or where is the problem exactly I have tested it in two different environments such as xampp and wamp with different PHP versions. PHP Fatal error: Uncaught TypeError: Symfon

nbukhari's avatar
nbukhari's avatar saedyousefPHP
6

Convert Sql Query to Laravel 8 Query

Help me to convert sql query to Laravel 8 select name, case WHEN iso2 = 'DE' THEN 'Germany' WHEN iso2 = 'CH' THEN 'Switzerland' ELSE 'India' End from cities When I convert DB::table('cities')->select('id as city_id', 'name as lang_name', 'ascii_name as city_name', 'iso2', DB::raw('(CASE WHEN iso2 = "DE" THEN "Germany" WHEN iso2 = "CH" THEN &q

babuyadhu's avatar
babuyadhu's avatar babuyadhuCode Review
2

Eloquent Query Not Working Where It works Otherwise

I have a couple simple Eloquent queries. The second one pulls data based on the first query. The first query pulls the correct data. The second pulls no data despite the same query working in my pgAdmin and in Tinker. The first query is in the following method: function getCompanies($def=0) { $user = Auth::user(); $companies = Collection::make([$user->company]);

denewey's avatar
denewey's avatar deneweyEloquent
5

Using query() in Eloquent

What difference does it make to use this when making a query to the db? I have a coworker who always puts this is his queries but I don't think it's needed as the query is executed the same with or without it. for example: What is the difference in these: $var= Model::query()->findOrFail($id); $var= Model::findOrFail($id); I'm just wondering what is the point of doing th

eggplantSword's avatar
eggplantSword's avatar automicaEloquent
4

How to efficiently map between an existing array to query Collection result?

I have an array containing several objects with keys initialized to 0 except for the first key in each object which is initialized to certain date: $data = []; for ($i=0; $i<4; $i++) { $data[] = (object)['main_key' => DateTime formatted string, 'key1' => '0', 'key2' => '0']; } I also have a query that groups by main_key and returns a result

cooperino's avatar
cooperino's avatar cooperinoCode Review
16

get data by last X number of days back with Query Builder

Is it possible to use query builder to get all rows going back in specified number of days? For example get all rows of the last 7 days, or last 30 days... etc Edit: I'm not sure this is correct: ->whereDate('created_at', Carbon::now()->subDays(7)) because it's only equal to

cooperino's avatar
cooperino's avatar cooperinoCode Review
2

Get all the users and their businesses in one query with a relationship

I have a relationship User->Business. I want to get all the row of the user and the business in one query. i.e. $query = User->Business, should give me something like this. [ user=>[ .... ] business =>[ ... ] ] I want to get all the users and their businesses in one query

kachi_dk's avatar
kachi_dk's avatar kokoshnetaLaravel
3

MySql 8 Query to Query builder

Is there any way to convert this query to a query builder? Because this returns an array. SELECT ref, type, question, answers_array FROM filled_forms, JSON_TABLE( answers, '$[*]' COLUMNS ( `ref` int(11) PATH '$.ref', `type` text PATH '$.type', `question` text PA

mohammednehru's avatar
mohammednehru's avatar mohammedne...Laravel
2

Query returning results on local but returning empty on live

I have gone into this strange issue, I exported the database from the staging server to local and just import it, and boom it is showing data, but in the case of the staging server it is returning an empty result for the following query. $categories = \DB::table('floor_plans as fp') ->join('unit_type_details as utd','fp.unit_type_detail_id','utd.id')

Inquisitive's avatar
Inquisitive's avatar sr57Laravel
15

Building query with scopes on a model

I'm not sure if this is possible but I'm trying to retrieve a users based on some true/false fields in the system. User model public function scopeRegularUsers($query): mixed { return $query->where('type_id', '=', UserType::REGULAR); } Repository $data = new User(); if (true) { $data->regularUsers(); } $data->filterBy($request) -&

MPesic's avatar
MPesic's avatar MPesicLaravel
3

Query Works - How to optimize it further.

Spoiler: Since I'm a beginning I assume the answer is, absolutely yes. Tables Job_Types and Job_Type_Listing Goal: A user is able to view a list of Job Types and click on them. However, I only want to display Job Types that actually have at least one listing. $jobtypes = DB::table('job_types') ->join('job_type_listing', function($join) {

gerardw85's avatar
gerardw85's avatar gerardw85Eloquent
2

Run query WithSum() for sub Withsum()'s result

User::where('id', 1)->withSum(['descendants' => function($query) { return $query->whereDepth('<', 10)->depthFirst()->withSum('nfts', 'price'); }],'nfts_sum_price')->first(); I'm trying to withsum() inside other withsum's results. When I try just sum id it works. But i need sum of ->withSum('nfts', 'price') this method what returns. I take "Undefined

denizgolbas's avatar
denizgolbas's avatar denizgolba...Eloquent
0

Laravel Pass Current Request Query String To Another Controller Method

i have a list of members i would like to export based on parameters in the current url request query string. for example, in the members_list page, /members?gender=men&day-born=friday. i can already generate and download the data doing so: /export?gender=men&day-born=friday how do i generate the export route keeping the current members_list page url request query strin

croftCoder's avatar
croftCoder's avatar croftCoderLaravel
4

Eloquent query results order - where before orWhere

I have a query like : $results = Terms:: where('name', '=', $query) ->orWhereJsonContains('synonyms', $query) ->orWhere('name', 'like', $query) ->orWhere('synonyms', 'like', $query) ->get(); Currently it returns the results in a quite random order. I want the results to come in the order such that matches for the wh

CLab's avatar
CLab's avatar CLabEloquent
9

Retrieve query from url in vuejs

Tenho um componente vuejs para buscar dados com filtros pelo método GET do próprio form: <template> <main role="main" class="flex-grow container max-w-7xl mx-auto px-2 py-4 sm:px-6 sm:py-6 lg:px-8 lg:py-8"> <div class="px-2 mb-3 text-sm sm:px-0"> <a class="primary-link" href="/">Home</a>

mvnobrega's avatar
mvnobrega's avatar mvnobregaVue
3

I want to use search query in my many to many relation!

Business Model: public function cat() { return $this->belongsToMany(BusinessCategory::class, 'cat_business', 'business_id' , 'cat_id'); } BusinessCategory Model: public function business() { return $this->belongsToMany(Business::class, 'cat_business','business_id', 'cat_id' ); } Search Query: public function s

Alex Fts's avatar
Alex Fts's avatar tykusLaravel
9

Laravel How to join 3 user tables while doing a where query

i need to join 3 tables, Expenses, Couples, Users and perform where and orWhere query on expenses. Below is the work flow: User signs up - user stores their firstname and lastname (column names - firstname and lastname) User invites their partner - by providing their partner's firstname, lastname (column names - firstname and lastname) and email. The information is stored in

singh's avatar
singh's avatar singhLaravel
2

Eloquent query returns empty if date is today

I'm trying to execute a simple query but the query returns empty when the date range is using today's date but in the database I can see there is data that should be brought. This is the query, on a model called Location $query = self::query()->where('customer_id', '=', sysconf()); if (isset($columns)) { $query->select($columns); } if

eggplantSword's avatar
eggplantSword's avatar NakovEloquent
1

Save query select

Hello, could someone help me with my functions, I want to save only one record of my queries and not save them all in my database, that is, I need to select only 1 and not save all of them, I am using Laravel livewire and Traits, I attach code. This is my save function in my component. public function saveResults() { try { DB::beginTransaction();

LIxPacoGg's avatar
LIxPacoGg's avatar LIxPacoGgLivewire
0

Query whereBetween with date and time

I want to query the data where created between this time 02:00:00 to 08:00:00, I tried below but don't get the data while i have some records created between that time, Kindly where i did wrong $start=Carbon::now()->format('Y-m-d')." 14:52:23"; $start1=Carbon::now()->format('Y-m-d')." 08:59:59"; // $tomorrow = Carbon::tom

melx's avatar
melx's avatar tykusLaravel
9

search query get request from form with pagination

so i have a form that sends a post request to a controller with the search query entered, this returns a paginated list of games that match the query, except since its a post request pagination wont work. so i changed the form to send the query as a get request i changed the route and controller but it only made a 404 not found error and didnt even get to the controller, id lik

nebulous0's avatar
nebulous0's avatar tykusLaravel
1

DB Query Help

hey guys looking for some help create a laravel query... basically I have a Course and User Models, I also have a Complete Model. courses have many Users Users have many Compete Complete belong to Users and Courses How get I create a query that returns a collection of all Courses with associated users and and wether or not they have completed it? looking into sub querys on lar

willnjl's avatar
willnjl's avatar tykusEloquent
3

Why does the $guarded attribute fire a additional select query

Hey Folks, During some performance optimizations, I noticed that update and insert statements via Eloquent trigger an additional query : "select column_name as column_name from information_schema.columns where table_schema = 'xxxx' and table_name = 'yyyy'" After some research I found this github issue post for the package "laravel-permissions": https://githu

DerAndi's avatar
DerAndi's avatar DerAndiEloquent
3

Query with relationship and limit

Hello, For example I have 3 types and I need to retrieve 4 productions of each type, so 12 productions at all. $types = Type::with(['productions' => function ($query) { $query ->where('published', true) ->orderBy('creation_date', 'desc') ->orderBy('created_at', 'desc') ->limit(4); }])->orderBy('order')->get(); But it ret

vincent15000's avatar
vincent15000's avatar vincent150...Eloquent
2

Are we have to sanitize our request when we use request in LIKE eloquent query?

Are we have to sanitize our request when we use request in LIKE eloquent query? if we have to sanitize it what are the best practices?

azizikri's avatar
azizikri's avatar azizikriLaravel
5

laravel merge multiple query

Hello Need merge multiple query, if it possible. Using 4 query. After search $seach gives product (using foreach in blade) and with a b c showing total prices for all products (in blade using {{$a}} {{$b}} {{$c}}) $seach = Product::where('cat', 'like', "%{$n}%")->latest()->paginate(50); $a = Product::where('cat', 'like', "%{$n}%")->selectRaw

Laracast13's avatar
Laracast13's avatar sr57Laravel
3

Query Builder with Model Attributes

I'm Using Query Builder, and I would like to use setters in my model, how can I do it please This is my code : $tenantUser = DB::connection('tenant')->table('users')->insert($request->validated()); the password method, doesn't work because i'm using query builder: protected function password(): Attribute { return Attribute::make( set: fn ($value) => Ha

AmineBHD's avatar
AmineBHD's avatar jlrdwEloquent
9

Access parent table column from subquery in update query

I have an average table and I want to update with subquery, but return this error thanks for help query : UPDATE averages a, (SELECT SUM(qty*cost) AS total_cost FROM averages WHERE averages.parent_id = a.product_id AND averages.invoice_id = a.invoice_id) as parent SET cost = parent.total_cost error : SQLSTATE[42S22]: Column not found: 10

mikailfaruqali's avatar
mikailfaruqali's avatar mikailfaru...Laravel
6

Run a different query if column value equals something else

I was wondering if there was a way to do this. For example, I have a properties table with a price and rental_frequency column. The rental_frequency column's values can either be daily, weekly, monthly or yearly. Would it be possible to do a different query on each row depending on their rental_frenquency value? Something like this maybe: If rental_frequency is daily then retr

alex29's avatar
alex29's avatar SnapeyEloquent
4

Help with an eloquent query that is using a join

I made this eloquent query: $games = DB::table('games') ->join('players', 'games.id', '=', 'players.game_id') ->join('users', 'players.user_id', '=', 'users.id') ->select( 'games.id as game_id', 'users.id as user_id', 'games.winner as winner', 'players.id as pl

johnrudolphdrexler's avatar
johnrudolphdrexler's avatar johnrudolp...Eloquent
4

How to query the parent according to the the sum of child fields?

I have two models Game and Round. A game has many rounds and a round belongs to a game. The structure is something like this. { "game":{ "title": "Game One", "description": "A game one", "rounds": [ { "title": "Round 1", "points": 10 }, {

sazvader's avatar
sazvader's avatar kevinbuiEloquent
4

First query really slow

I am currently having an issue where my Laravel app is really slow at loading when navigating. I checked Clockwork and messed around with different queries and have noticed that the first query on any page is 10x slower than if it is called later. Has anyone encountered this issue before?

AdamSims's avatar
AdamSims's avatar robbykrlosLaravel
31

pagination in a query

I want to filter the display of images with this query. And it works for me but the problem is with the pagination. I use Load More On Page Scroll. When I display images in a category and click load more, more images are displayed. But when I go to another category, like the previous category, more images are displayed without me clicking on load more. I want more images to be

shimana's avatar
shimana's avatar undeported...Laravel
5

How to add "fictional" column to query?

I am using Yajra Datatables and from what I see, it's not possible to perform a search query on an added column that's not on the SQL query. Right now I have the query: $model::select('column1','column2','column3')... Is it possible to add non-existing column and then I will manipulate it?

cooperino's avatar
cooperino's avatar cooperinoCode Review
13

Modify a query code

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->selected, function ($query, $selected) { $query->when($selected == '1', fu

shimana's avatar
shimana's avatar shimanaLaravel
2

Is this an excessive number of query calls?

Hi Guys, I am using AdminLte to build the admin panel. Using Laravel Debugber, I noticed that is doing like 50 query calls to load a simple view like 'show products', where I am displaying all the products from the DB. The weird thing is that, apart for like 5-6 queries, the rest (so around 45) are all this same query: select * from `roles` where `roles`.`id` = 1 limit 1 An in

Kris01's avatar
Kris01's avatar Kris01Laravel
7

Create multiple models using one join query

Just being curious, but is it possible to do one query, joining parent and grandparent tables, and create multiple model instances (child, parent and grandparent model) from the query result? It would save me 2 queries.

bhosted's avatar
bhosted's avatar bhostedEloquent
2

How can I calculate certain variables directly from the query?

Hi, I would like to calculate two variables in this query, namely dismissed and clicked if they are true, how can I do that? May? $query = Popup::query() ->where('client_id', $clientId) ->whereBetween('created_at', [$startDate->format('Y-m-d')." 00:00:00", $endDate->format('Y-m-d')." 23:59:59"]);

KikoLdasd's avatar
KikoLdasd's avatar SinnbeckLaravel
1

How to query multiple tables in one go?

Hi. I want to get total number of users (for past month) and total_number of posts (for 2 months ago). How can I use one Mysql query to grap above information?

danimohamadnejad's avatar
danimohamadnejad's avatar SinnbeckGeneral
10

Query with inner join in Eloquent

I need help with this query in eloquent, somebody could help me to convert it? SELECT categories.name, COUNT(*) AS total FROM answers INNER JOIN questions ON answers.question_id = questions.id INNER JOIN answer_application ON answer_application.answer_id = answers.id INNER JOIN dimensions ON dimensions.id = questions.dimension_id INNER JOIN domains ON domains.id = dimensio

Josadec's avatar
Josadec's avatar MohamedTam...Eloquent
14

Need help with data query and display exercise

Hi everyone, I'm a newbie and I'm researching to do this exercise, hope it helps. I have 3 entities Project (id | name) Group (id | name | project_id) Staff (id | name | group_id | project_id) Relationships Project has many Group Project has many Staff through Group (I think it solves the problem, but it doesn't seem to be any different from a regular relationship like hasMa

MinatoXX's avatar
MinatoXX's avatar SinnbeckEloquent
5

Laravel Eloquent Query, is this too complex?

Ok so, here is a little background of my db: - Products Table (price, discount, category_id ...) - Categories Table (image_path) - Products Languages Table (description, title, language_id) - Categories Languages Table (category name, language_id) I've done it in this way because this web app supports multiple languages. In the snippet down below I am getting the product inform

Kris01's avatar
Kris01's avatar kokoshnetaLaravel
8

Limit Spatie Query Builder relationships based on a condition?

With Spatie Query Builder I define the allowed includes: ->allowedIncludes(['roles', 'media']) Then on the API resource I only allow roles to be sent if user is an admin: 'roles' => $this->when($request->user()?->hasRole('admin'), $this->roles) This works and prevents general users of my API to see other user's roles. Yet, they still can request the relation

panthro's avatar
panthro's avatar panthroLaravel
0

mysql query to get count

i have tables like below, tests id, test_name, test_code, course_id upload_answers id, test_id, file_name Now i want to fetch total upload_answers count for a particular course. Example: if course_id = 1 then i want to fetch total upload_answers which matches course_id = 1 tests i tried like below, SELECT t.test_id,t.test_name,t.test_code, (SELECT COUNT(test_

Deekshith's avatar
Deekshith's avatar DeekshithGeneral
4

A when() query doesn't work properly

Hey! I have this query ReviewReports::when( isset($reason) && $reason != "all", fn ($builder) => $builder->where('report_reason', '=', $reason) )->when( isset($status) && $status != "all", fn ($builder) => $builder->whereHas('review', fn ($builder) =>

lat4732's avatar
lat4732's avatar SinnbeckLaravel
7

Query to show data checkin , checkout, breakin breakout in 1 row

Hello, i need help. i already try many query using case, etc. but still not solved. i have structure data like this : ID;User_ID;CheckinTime;Date; 1|110|"2022-03-25 06:30:29"|"2022-03-25"| 2|110|"2022-03-25 12:12:31"|"2022-03-25"| 3110|"2022-03-25 13:02:11"|"2022-03-25"| 4|110|"2022-03-25 17

Dikurr11's avatar
Dikurr11's avatar SilenceBri...Laravel
3

Build availability query for reservation system

Hello everybody, I'm building a booking system for a smart locker, where the user can reserve through the website a locker for a date and X hours. I have a model Reservation and a model Box, I'm trying to query all the reservation that are already in place for the dates and time requested from the new user. Example: You want to book a locker and you search free locker on 07/04/

Givar's avatar
Givar's avatar GivarCode Review
8

conditional based in one query

i have a table like below, courses course_id, course_name, course_code Now every course has tests and it has table structure like below, test_id, test_name, course_id, test_schedule_date, description, syllabus,test_type Also every courses has packages packages package_id, course_id, package_name Now we should map every test to particular package and it has below structure, t

Deekshith's avatar
Deekshith's avatar DeekshithLaravel
1

Inertia query params are empty on the backend

Hi guys! I am using Inertia with vue 3. And it works ok locally. But on production I am unable to read query params on my laravel backend. $_GET is empty. Can't figure out what is wrong. I'd appreciate any help. I am making get requsests via Link and Inertia.get method. I am able to see query params in browser address bar, and also see them in console. But on the backend $_GET

MirasMustimuly's avatar
MirasMustimuly's avatar MirasMusti...General
3