Convert raw query into more eloquent/query builder way

Hi. i have this raw query in laravel. Can someone help me to convert my raw query into eloquent/query builder approach? $ssRaw = DB::select(DB::raw(" SELECT ts.serviceName as servicename , COUNT(*) AS totalservices FROM tbl_services AS ts JOIN ( SELECT REPLACE(TRIM(LEADING SUBSTRING_INDEX(t1.serviceType,',',se

onieyanihh's avatar
onieyanihh's avatar Tray2Laravel
2

query bug?? rounded to the closest integer?

in the following case: in query: 20+80+90 (we all know gives 190) / divided by 25 (we all can calculate and gives 7.6) in the case of setting every column on phpmyadmin to decimal 5,2 and trying to perform a query in wich case can this happen? (give a result of 7.0) instead of 7.6, tried both eloquent | normal query| and created view, all the querys gave 7.0 instead of 7.6 , wh

peterpan26's avatar
peterpan26's avatar lbecketLaravel
8

sql query while inserting in laravel gives error

$query = "insert into users (balance,i_customer,name,password,porta_domain,email,phone_number,company,added_by,customer_id,num_failed_current,fname,lname,zip_code,street,city,state,country) values (0 ," . $i_customer . ", '" . $cid . "' , '" . $defaultPassword . "' , 'ach.com', '" . $contact[0]['email'] . "', '" . $contact[0]['

vimal1999's avatar
vimal1999's avatar SinnbeckLaravel
1

query shows up critical mistake.

hello , the last few days i've been trying to do a query, that sums values from two tables and then after it divides them by one column of one table. what's happening it's giving a wrong result and i have no idea why 'costKM' => Repairs::query() ->join('form', 'form.carplate', '=', 'form.carplate') ->selectRaw(&q

peterpan26's avatar
peterpan26's avatar peterpan26Laravel
3

Rewrite the query builder statement

Hi guys , my code didn't work can you correct it should be select * from courses where course_id doesn't exists in batches and course_interest table. $courses = DB::table('courses') ->whereNotExists(function ($query) { $query->select('course_id') ->from('batches') ->whereColumn('batches.course_id',

demonz's avatar
demonz's avatar demonzEloquent
4

Why is my query not returning what I expect?

Hello everyone, let me explain my problem, I am passing by postman the following: { "data": { "from": "2022-12-01", "to": "2022-12-02" } } But this query is returning all the records without taking into account the conditional whereBetween, why is this happening? $signings = Employee::with(['working_hour_

Alejo's avatar
Alejo's avatar SinnbeckEloquent
17

how do i do this in query

hi, i got a query but it's not summing well the "kmsTotais" , how do i fix this ? and make it to work? i yesterday asked how to do the sum and subtraction in elloquent to then try to pass it onto the selectraw like i did with abastecimento, but it didnt work, please help fix this , thanks $totalEurosAbastecimento = $quilometragem->sum('abastecimento_euros');

peterpan26's avatar
peterpan26's avatar peterpan26Laravel
3

How change the mysql query in query builder Laravel?

Hi guys, i using this query on mysql and work, when y try to convert to query builder the whereBetween() dont accept the date as a column and that spected. my question is how i can use the incoment date as column to search between start_date and end_date columns. Mysql command SELECT r.id_user, 1 as vacation, r.resume FROM register r WHERE "2016-11-16" BETW

hcastillo's avatar
hcastillo's avatar hcastilloLaravel
7

Preserve initial eloquent query for multiple counts

I have a model Disponibilita, I want to make different queries to count number of rows based on a particular status to populate a chart in my frontend. The code I use is the following: //This is common for all queries $count = Disponibilita::whereYear('departure_date', $year); // Start counting $totals = [ "available" =>

wassy83's avatar
wassy83's avatar wassy83Eloquent
11

Get request->all without query params

When I do this $req = $request->all(); I just want to get the request body, but this is also adding any inline params I might add, for eg this ?q=1 is also in the request array that comes back. Whats the proper way to do it, so it ignores query params ?

makapaka's avatar
makapaka's avatar SinnbeckLaravel
8

Very slow query

I am dummy with sql. $data = $model1->model2()->has('model3')->withCount('model3')->orderBy('name')->get(); If model 3 has up to 2M rows, indexes in id's. Model1 and model2 pivot with indexes od id's. Why does it take up to 5000ms to query it? Is there a better approach? Thanks select `model2`.*, ( select count(*) from `model3` where

HUGE_DICK_10_INCHES's avatar
HUGE_DICK_10_INCHES's avatar Tray2Laravel
3

Query builder search with sub query

$item_id = $request->item_id; DB::table('sales') ->join(DB::raw('(SELECT GROUP_CONCAT(stocks.name) AS item_name,COUNT(stocks.id) AS total_item ,sale_items.sale_id FROM stocks INNER JOIN nso007_sale_details AS sale_items ON sale_items.item_id = stocks.id GROUP BY sale_items.sale_id) sale_items'), 'sale_items.sale_id', 'sales.id'); How can I filter by item id a

ZandA's avatar
ZandA's avatar ZandALaravel
0

OderByRaw not returning results, but working fine elsewhere for similar query

I am trying to get "Top 10" results for travel destinations: Countries, Cities, Things to Do. I am using the query builder and OrderByRaw to order destinations by "Upvotes - Downvotes" so the top 10 user voted destinations in each category will be fetched from the DB. This is my basic query : Destination::where('type', '=', 'destination_type')->orderByRaw

Scotalia's avatar
Scotalia's avatar ScotaliaEloquent
12

How can I count from two tables in my eloquent query?

I have two table named 'customers' & 'linkeds', now I want to get the count of 'customer_name'. Below is my query. $this->linked = $this->linked->join('customers', 'linkeds.customer_id', '=', 'customers.id')- >where('customer_name', $customer_name)->get();

Syed1980's avatar
Syed1980's avatar Syed1980Eloquent
5

Query Tags

When a user types keywords for the search filter, I want to query tags for a collection. If the keyword is "water body" the result of the filter should return collections that have both tags (water and body). My code below shows results that has either of the tags public function searchFilter(Request $request, Collection $collection){ $terms = explode(" ", $

allanjiru's avatar
allanjiru's avatar MohamedTam...Laravel
1

join query sum fetch error

hi, i got the two following tables table name : formulario id primary bigint(20) mes_ano varchar(255) matricula varchar(255) table name: reparacoes id primary bigint(11) matricula varchar(255) valor varchar(255) what i want to accomplish is joining on the valor with formulario's values and fetch the data in laravel view dashboard. i tried the following way, but with the join it

peterpan26's avatar
peterpan26's avatar peterpan26Laravel
4

How to target letters with "Umlaut" in SQL query?

How can I specifically target the umlaut letters? Currently, the database ignores the umlaut completely: ö is treated as o, ... If I type "böse", it returns "böse" and "bose". That is not what I want, only "böse" should be returned. I have tried changing the database config, specifically the 'charset' and 'collation' attributes. I have se

ivymasterman's avatar
ivymasterman's avatar peterpan26Eloquent
5

QUERY RESULTS FETCH ON LARAVEL

hi, i got the two following tables table name : formulario id primary bigint(20) mes_ano varchar(255) matricula varchar(255) table name: reparacoes id primary bigint(11) matricula varchar(255) valor varchar(255) what i want to accomplish is joining on the valor with formulario's values and fetch the data in laravel view dashboard. i tried the following way, but with the join i

peterpan26's avatar
peterpan26's avatar peterpan26Laravel
0

can this query be optimized

i am busy with a pricewatch script/page and i am trying to optimize my qeurys. For some I have changed the db setup and that works fine. Only query for now is the following one. The query should get the latest price of the combination webshop, product and size. For now this is the query that takes longest, and I would like to optimize it. This is an example of 1 product and siz

marnixk77's avatar
marnixk77's avatar Tray2Code Review
1

how to make a join here without messing up the query sum?

hello i got the following code that produces a sum correctly done, but whenever i try to join the table to another to concat some results in the same dashboard it douobles up the values. $data = Reparacoes::select(DB::raw("SUM(valor) as sum, matricula")) ->orderBy("matricula")

peterpan26's avatar
peterpan26's avatar peterpan26Laravel
2

Laravel : eloquent query

Hi. I'm trying to do an eloquent query, where i have a member table, and a booking table. my goal is to get the member info and the total paid on the booking table. So i did this The MemberController : public function index(){ $members = DB::table('bravo_members') ->join('bravo_bookings', 'bravo_members.user_id', '=', 'bravo_bookings.customer_id') -&g

Nicho's avatar
Nicho's avatar NichoLaravel
11

Sql Query

Hello everyone I need to write this query This is the pdf that you can check the query https://drive.google.com/drive/folders/1XKvsbN9GE3-OQZtZBELE-Mhr7mBcAt4Z

Mohamed_Hammad's avatar
Mohamed_Hammad's avatar Tray2Laravel
6

Eloquent query for retrieving data from Partitioned Table

How to Write Eloquent query for retrieving data from Partitioned Table I am working on database partitioning and using laravel framework for development. I am trying to convert raw query to Eloquent. Ex. My query is like SELECT * FROM employee PARTITION (p2) so using raw queries its like $results = DB::select( DB::raw("SELECT * FROM employee PARTITION (p2)") ); I am n

Russelmhardy's avatar
Russelmhardy's avatar SinnbeckLaravel
4

Error while sending QUERY packet in Lumen queue

I'm calling a common function from Lumen queue and cron job. During user interaction this function is called from queue ( async purpose ), if something goes wrong with queue execution for example lock wait timeout etc. I have a cron job which is scheduled for every 15 mins to process older transactions. There is no issue when cron is executed but whenever queue is executed MySQ

dmahesh1705's avatar
dmahesh1705's avatar dmahesh170...Lumen
0

Query Scope: Filter items, who got relations with specific condition

Hello everybody, I'd like to solve the following problem: I'd like to create a query scope for my model called Ticket. This Ticket hasMany replies (Reply). And each reply can have a status. Now I'd like to create a scope on the Ticket model, which should filter all tickets having more than 2 replies in the status UNREAD. In my case, the status itself is just an PHP-enum called

ahoi's avatar
ahoi's avatar ahoiEloquent
4

can someone tell how to translate theis sql query to laravel query builder

I want to write this sql query into laravel query builder SELECT orders.invoice_number,u.first_name,u.last_name,orders.order_date, (SELECT sum(products.price*order_items.qantity) FROM products INNER JOIN order_items on products.id=order_items.product_id WHERE order_items.order_id=orders.id) as total,(SELECT users.first_name FROM users WHERE users.id=u.referred_by

mohamadAbdelhady's avatar
mohamadAbdelhady's avatar jlrdwLaravel
7

Issue with whereRaw syntax query

Hi all. Trying to filter some data for a query, below is currently what I'm using to filter out data that only has the transaction_type of 'Buy' $total_sales = DB::table('transactions') ->whereRaw("transactions.transaction_type = 'Buy' ") ->sum('transactions.gross_value'); However whenever I dd th

Tchopa's avatar
Tchopa's avatar TchopaLaravel
7

How to use Query Builder to have a left join inside a right join

I have a query that works and I am trying to get this into Query Builder: SELECT tblMap.Site, CASE WHEN tblmap.mapelec = 'TRUE' THEN 'Digital Maps' WHEN tblmap.mappaper = 'TRUE' THEN 'Paper Maps' ELSE 'Other Maps' END AS MapType, tblMap.MapScopeCode, tblMap.MapScopeNum, CASE WHEN tblmap.mapplan = 'TRUE' THEN 'Plan View' END AS MapPlan, tlkpMapProfTyp.MapProfTypD

stanhook's avatar
stanhook's avatar stanhookLaravel
15

How to Make Laravel Eloquent "IN" Query?

I want to make query in Laravel Eloquent like here its raw MySQL query SELECT * from exampleTbl where id in(1,2,3,4) I have tried this in Laravel Eloquent but it's not working DB::where("id IN(23,25)")->get()

JernalinSadhoo's avatar
JernalinSadhoo's avatar webrobertEloquent
3

My query updates even though I don't change it.

I'm using this logic to get sales from this week and last week. I added a $minus value to substract number of weeks from the query in getWeekly method. And I'm only using getData() to broadly filter my data in order to re-use it by calling and saving it once. However, this code only runs once and it breaks after first run. I can switch $this_week and $last_week's places and no

motinska94's avatar
motinska94's avatar cwhiteEloquent
5

multiple condition in same query

i have a requirement where it requires multiple where conditions in single query and i want to pass each condition to each variable to display the stats. i have a table like below, nominations table id, user_id, status, file_name now i want to get accepted, pending, rejected nominations. in single query so i tried query like below, $nominations = Nomination::where('submited',1

Deekshith's avatar
Deekshith's avatar DeekshithLaravel
2

How to send a query to a database given a two dimensional array?

Hello, dear forum users! I am unable to complete the task: You need to send a query to the database, given a two-dimensional array. There is such an array ($activeAttr): array:2 [▼ "age" => array:2 [▼ 0 => "adults" 1 => "children" ] "color" => array:1 [▼ 2 => "black" ] ] public static f

Rediska's avatar
Rediska's avatar RediskaEloquent
4

Is there a way to add a where clause the query everytime it's called?

I have city_id in my users table. And every user has more than one product. Is there a way to automatically add whereCityId(Auth::user()->city_id) to all my queries? I know I can make a hasone method in the model but I want it to be included as soon as I make a query for it. In simpler words, I want to get Model::whereCityId(Auth::user()->city_id)->get() when I type Mo

motinska94's avatar
motinska94's avatar motinska94Eloquent
2

Update multiple values using a query

this is my mistake General error: 1366 Incorrect decimal value: '["4.00","3.00"]' for column 'quantity_final' at row 1 (SQL: update `servis` set `quantity_final` = ["4.00","3.00"] where `id` in (1077, 1078)) What I make a query to my two tables and the data that I call in this case is a quantity number for both, the data that is displaye

Jose_DIAz's avatar
Jose_DIAz's avatar SnapeyLaravel
1

How to add an "if" condition to a query?

Could you tell me, please, is it possible to add an "if" condition to the query? I have a request: public static function getAllAvailableAttributes ($categoryTitle, $attrBrand, $attrColor) { return static::select(['brand', 'color', 'size']) ->where([ ['stock', 'in-stock'], ['brand', '!=', ''], ['color', '!=', ''], ]) ->where(function

Rediska's avatar
Rediska's avatar RediskaEloquent
13

Implement `with` using query builder

I have tables with relationships like the following: users: id | name 1 | John posts: id | title | user_id 1 | Post 1 | 1 comments: id | comment | user_id | post_id 1 | comment 1 | 1 | 1 I want to fetch the posts and comments related to a specific user using query builder without repeating the user data with every post, Just like how with works. So that p

mdev11's avatar
mdev11's avatar MohamedTam...Laravel
10

Laravel Query

I have a car rental system, i need to return available cars on this endpoint "get available cars". the body contains "receive_time" and "deliver_time" which i will check my orders in db if it contains orders on this time or not. i need to write the correct query for this case i can't do it. code $cars = $this->car->query()->where('garag

Mohamed_Hammad's avatar
Mohamed_Hammad's avatar Randy_John...Laravel
6

How To Query Another Table if Key Value exists in Current Model

I have a list of fire department apparatus that shows their current status. The page refreshes periodically. The query that displays the apparatus status has a field in it for an IncidentID. If an apparatus gets assigned to an incident the IncidentID that you see there is the ID of the incident found in a separate incidents table. I need to figure out how to display a few

phayes0289's avatar
phayes0289's avatar phayes0289Laravel
9

whereRelation query

how to query value as a type of array for query whereRelation eloquent relation https://github.com/laravel/framework/blob/9.x/src/Illuminate/Database/Eloquent/Concerns/QueriesRelationships.php public function whereRelation($relation, $column, $operator = null, $value = null) { return $this->whereHas($relation, function ($query) use ($column, $operator, $valu

smacy's avatar
smacy's avatar LumethysLaravel
2

I used Eloquent query to extract the below table

$ user = User::all(); id q1 q2 q3 q4 q5 q6 q7 0 0.01 0.8 null 0.9 null 0.9 0.1 1 0.8 null null 1 null 1 null 2 null null null 0.03 null 0.03 null 3 0.04 0.4 null 0.9 null 0.5 null 4 null 0.67 null 0.8 null 9 0.8 5 0.07 0.9 null 0.6 null 10 null 6 1 null null 1 null 0.05 null The final tabl

harini_ravi4's avatar
harini_ravi4's avatar vincent150...Laravel
2

Query distributed by review stars not showing proper results

Hey everyone! I have this query $reviewArr = Review::select('stars', \DB::raw('COUNT(*) as total')) ->where('website_id', $request->website_id) ->groupBy('stars') ->pluck('stars', 'total'); which is supposed to print an array like #items: array:1 [▼ 1 =&

lat4732's avatar
lat4732's avatar LaralexLaravel
7

How can I achieve the following data structure with Query Builder (And not Eloquent's Relationships)?

I have 3 different tables. Table1 is the "main" table with primary id key. Table2 and Table3 use Table1 id as foreign key table_1_id Let's say I have some query from Table 1: Table1::where(...)->where(...)->get(); which gives me 100 results. Then I want to get all the data from Table2 and Table3 with the IDs of Table1, but still have 100 results, so a structure

Ligonsker's avatar
Ligonsker's avatar jlrdwCode Review
4

Query Builder question

Hello Team, I have this query which work fine. public function render() { $mypasswords = MyPassword::query($this->search) ->select('id','title','user_name','url','notes','password','user_id') ->orWhere('id','like','%'.$this->search.'%') ->orWhere('title','like','%'.$this->search.'%') ->orWhere('url','like','%'.$

david19's avatar
david19's avatar david19Laravel
2

How to use Carbon in Laravel query?

Hi, I wanna get month with Carbon in laravel query, but I am getting error message: "Property [Carbon] does not exist on the Eloquent builder instance." Here's what I have tried so far- $data = Profilpenper::join('nilaipers', 'nilaipers.profilpenpers_id', 'profilpenpers.id') ->where('profilpenpers.updated_at', 'like', $year.'%')

aidil's avatar
aidil's avatar SilenceBri...Laravel
7

Can I join with a query builder instance?

I have a query builder statement: $builder = DB::table('my_table')->where(...)->where(...)->orderBy(...)->orderBy(...) but without the get() at the end. Then I wanted to join the result that would outcome from this query: $data = $builder->join('second_table'), function($join){ $join->on('my_table.id' = 'second_table.my_table_id')->where(...); })->g

Ligonsker's avatar
Ligonsker's avatar LigonskerCode Review
2

Larvel foreach N+1 problem with query

Hello, i am working with relations table for moment and i need to query between them. So i have a table team with some skills , and i am biulding a report to count all teams with same skills, so this work: $skills = Skill::get(); $countSkill = []; $skillField = []; foreach($skills as $skill) { $skillField[] = $skill->skills

Maison012's avatar
Maison012's avatar Leon012Laravel
6

How to specify "or" conditions in a JSON API query string?

For now, all of my requests to the backend were in an "AND" relationship. For example, if I want to get a user with the "firstName" of "Alex", and that is employed in a company "FB", I would write: http://localhost:80/api/users?filter[firstName]=Alex&filter[company][name]=FB How would you specify an "or" relationship in the

ivymasterman's avatar
ivymasterman's avatar omardevGeneral
1

problem with appending query string

So I'm having a problem with appending the query string to the results, I have simple filtering (select/dropdown) in my form and a search box (input). The problem is the result of the steps below: IF I do select an item in the dropdown and then; Type to search input, and press the SUBMIT button, the query string from the dropdown is removed and only the query string from th

NoLAstNamE's avatar
NoLAstNamE's avatar benjamin15...Laravel
5

Laravel Query Builder Issues

Hello. What's wrong with this query? I can't seem to get it return the correct value $offerwall = OfferWall::find($offerwall)->with(['offer'=> function($query) use ($proxy_results) { $query->where('status', 'active')->whereHas('targeting', function($query) use ($proxy_results) { $query->where('targeting_details.countries_allowe

bremmurd's avatar
bremmurd's avatar bremmurdLaravel
2

Help with whereDoesntHave query

Hi, I'm trying to create a table which holds records of assigned cars. So when a car is assigned to a booking it gets added to this table assigned_cars which is a Car belongsToMany Bookings relationship. But what I'm struggling with is the query to get available cars. Doesn't matter what the pick-up date is. It seems like if the car has already been booked once before that it w

hjortur17's avatar
hjortur17's avatar hjortur17Eloquent
3