Mysql query returns error

I am learning Charts in Laravel, i need to draw Line graph for daily Student Attendance for those students come late or on time. I tried to write MYSQL query but it doesn't work I tried subquery on same table to get data for daily students and i also need 7 dates only not full date, like date is stored in db as 09/08/2019 but i need it as 08 as date. SELECT Date, COUNT(*) AS Ti

CharlesK's avatar
CharlesK's avatar CharlesKGeneral
2

Laravel eloquent query not working

I want to write a query using the Laravel Eloquent structure. I want to query these values ​​after entering the min and max values. I have a product table. Column product table with discount (%) and price. I want to get min and max value according to the discounted value of the product. I want this to be based on the query including the discount. $products->where('price', '&

JannatKhan's avatar
JannatKhan's avatar JannatKhanEloquent
2

Yajra Datatables Query method being compatible error

If anyone has used the Yajra Datatables package, I am getting this error. ReflectionException: Class App\DataTables\UsersDataTable does not exist Caused by ErrorException: Declaration of App\DataTables\UsersDataTable::query(): Illuminate\Database\Query\Builder should be compatible with Yajra\DataTables\DataTables::query($builder) From what I see both query methods are returni

jrdavidson's avatar
jrdavidson's avatar xtremer360Laravel
19

Passing query data in Pagination page

Hello I'm trying to filter homes by Price Ascending, Price Descending, Most Recent, A-Z, Z-A and I've have success with exception to the fact that the queries to not persist after the first page. Can anyone please show me a way to do it? public function index(Request $request) { $query = $request->input('queryOptions'); if($query == 'Price Ascending'){ $prop

vlauciani94's avatar
vlauciani94's avatar tisuchiGeneral
1

Query Laravel nested relationship [Laravel 6.x]

I have a page in my Laravel app where I am fetching categories and eager loading events in that category. It works fine. What I want to do now is to fetch the categories alright but this time, fetch events based on a region/location selected by the user. These are the models I am working with; 1.Region Class <?php namespace App\Models; use Illuminate\Database\Eloquent\Mode

petersowah's avatar
petersowah's avatar petersowahEloquent
10

Query to eloquent converter

Hello This is the sql query but how can I do it in laravel? SELECT id FROM clientes where email is null and phone is null and id in(SELECT DISTINCT(cliente_id) FROM reservas WHERE dia < now()) This is the code that I tried to make: $usersIdDel = DB::table("clientes")->select('id') ->whereNotNull('email')->whereNotNull('phone') ->

kaizokupuffball's avatar
kaizokupuffball's avatar kaizokupuf...General
4

Laravel many to many Query

I have a many to many associations on Vendors and Orders models whenever I try to query the vendor Id I get Argument 1 passed to Illuminate\Database\Query\Builder::cleanBindings() must be of the type array, int given My code is public function orders() { $vendor_id = Auth::guard('vendor')->user()->id; $orders = Order::whereHas('vendors', funct

itamelions's avatar
itamelions's avatar itamelionsLaravel
2

Query Table and Return Results based on a different table.

Hello All, First time posting on here. I am stuck at the moment. What I have are 3 models: Locations, Items, Stock. A relation is made between the Items and Stock then Stock has a relation to Locations. What I am looking to do is return all Items (the model) that has Stock given the location. I have all of the models set up and the FKs configured between the tables but I don't

nhalstead's avatar
nhalstead's avatar nhalsteadEloquent
2

Automatically decode json from query builder results

Hi, I'm using the query builder to get data from an external database. I get a lot of nest json data (some fields are mysql json), i would like to find a way to automatically decode the json fields into arrays so i can work on them properly. Output example from query builder : { id: 1, production_companies: "[{"id":9993,"logo_path":"\/2Tc1P3Ac8M479

Estev's avatar
Estev's avatar NakovLaravel
1

Variable in query

I want to use variable $number in query like DB::select (..... whereIn ("id", $number ) ..... ) How write it correctly? may be in quotes?

777not888's avatar
777not888's avatar jlrdwLaravel
3

Query by month and year

I'm trying to query by the month and year but it's returning an empty array for each month. Here's my function public function month($id, Request $request){ $user = auth()->user(); $month = $request->month; $year = $request->year; dd($times = Time::where('user_id', $id) ->whereYear('start_day', $request->year)

lara121081's avatar
lara121081's avatar myke2424General
2

How to use search query in the view?

I wana pass search query to laravel view, for now i try use compact and compatct-> with but still results it's this same "Undefined variable: query" I try pass hard code like single query "abc" , "bcd" etc. it's work /kategoria?query=abc&page=2, but when i wana make some like wildcart then i cant pass variable query to view. Search Controlle

MysZon's avatar
MysZon's avatar MysZonLaravel
2

Need Help On Translating a Complex Query into Laravel

Because I am old school, I have a habit of first building my queries in SQL, and then translating them into Query Builder. But this one beats me. The SQL works fine. But can someone offer some guidance on getting into Laravel? What is messing me up is that there is a subquery and two joins plus a sum(). I noticed that Laravel 6 now offers subqueries, but they are super simple

vincej's avatar
vincej's avatar jlrdwLaravel
4

Update query in bulk using single query instead of using loop

Hello all, I'm looking for a query to update in bulk using single query instead of using query in loop. I have more than 2000 products and I have to update there stock value. Right now I using loop for each product so its take too much time. Here is my code foreach($products as $srcProduct){ $product_id = $srcProduct['product_id']; $o

V9द's avatar
V9द's avatar RobstarLaravel
5

Search Query in Serialize data

Hi, I have multiple selected dropdown data_tags[] & I stored data_tags[] in the database as Serialize. Now I want to search in them by the same dropDown multiple selections. In this case, data should be fetched which has minimum one data. or an example: Suppose I have a product named HTML with tag name LANGUAGE, and another product name is PHP with tag name LANGUAGE, PROGRA

mdrathik's avatar
mdrathik's avatar BraunsonLaravel
1

Laravel get month name by query

Imagine today's date is 6/15/2019 I'm trying to query my visitor with in this year of 2019 only. Then, I want to know how many are on 1,2,3,4,5,6 I've tried $raw = Visitor::query() ->whereYear('created_at', now()->year -1) ->get() ->pluck('created_at'); $data = []; foreach ($raw as $i=>$date) { $data[$i] = Carbon::parse($date)->format('m'); if( Carbo

CharlesK's avatar
CharlesK's avatar tazimul_lc...General
7

Query Builder not working with PDO::ATTR_EMULATE_PREPARES

Hello. We are planning to migrate existing software gradually to use Laravel. Our database is PostgesSQL. I am currently trying to implement Passport to our system. Our database connection apparently need to have option PDO::ATTR_EMULATE_PREPARES => true for some bouncer thing, not entirely sure why, and that is not up to me. Having this option set causes errors with Query b

Armoletti's avatar
Armoletti's avatar ArmolettiLaravel
1

sql query to get best sellers in ecommerce within a date range?

`` Hello I have folowing tables in my ecommerce application: products id name orders id userid total created_date order_items id orderid productid quantity how can I query database to get best seller products which have been ordered within specific date range like 2019-10-1 to 2019-12-5? ``

danimohamadnejad's avatar
danimohamadnejad's avatar danimohama...Laravel
3

Undefined variable: request in Laravel query

I want to create a nested parameter grouping in my query where the value of one of my nested where is gotten from the form. if($request->view != '') { $query->where(function ($query) { $query->where('type', $request->view) ->orWhere('type', 2); })->get(); } Right now I'm getting Undefined variable: request. How do I get the value of $r

ivanleoncz69's avatar
ivanleoncz69's avatar ivanleoncz...Laravel
2

Difficulty translating a query to Query Builder

I am working on an ajax query shown here: SELECT * FROM users join provider_user on users.id = provider_user.user_id WHERE provider_user.provider_id = 16 (this will be a parameter) AND (firstname LIKE '%r%' or lastname LIKE '%r%' or email LIKE '%r%') This returns 3 results which is correct - no duplicates. However, when I convert it to Laravel Query Builder it is returning a

ctyler's avatar
ctyler's avatar jlrdwEloquent
2

How to count query in Laravel Eloquent?

Dear Developers, I need to count and specific item from mysql using laravel eloquent query for current month. Like when i will count item it will count only for that month . but I unable to do that . I am just trying to count only but its not working too :( $attendence= DB::table('students') ->join('attendences', 'attendences.student_username', '=', 'students.use

masumluf's avatar
masumluf's avatar SinnbeckLaravel
2

How to Get All Orders of Customers Assigned to a Specific Agent Using Query Builder

Hey guys, I've been pulling my hair strings for days trying to write this query but just not getting anywhere. I have these tables: Agents (Users) id name email password created_at updated_at Customers id agent_id firstname lastname email created_at updated_at Orders id customer_id product_id amount Customer

sargbah84's avatar
sargbah84's avatar design_stu...Laravel
8

Eloquent query with subquery on pivot

I have two eloquent models: User, Project with a many-to-many relationship between them. In User model: public function projects() { return $this->belongsToMany(Project::class)->withTimestamps(); } and in Project model: public function users() { return $this->belongsToMany(User::class)->withTimestamps(); } The database tables are user, project and the pivot

csm2pk's avatar
csm2pk's avatar csm2pkEloquent
2

How to get query response time in laravel?

Hello During executing my query, I want to know the time how many seconds the query has taken to execute like symfony.

vlauciani94's avatar
vlauciani94's avatar vlauciani9...General
2

combine two query into one

i want to have one foreach loop in blade view after combine this query $balanceQty=CashSales::with('items') ->select('item_id',DB::raw('sum(quantity) as tqty')) ->whereIn('l_type',['credit_sale','cash_sale']) ->groupby('item_id') ->get(); $balanceQty1=CashSales::with('i

melx's avatar
melx's avatar vanshu654Laravel
12

How to conditionally add query in Eloquent?

Considering this database design: Employee hasMany Contract, In reverse Contract belongs to an Employee and also Contract belongsTo Job and Department. Now I have this query: $query = "SELECT DISTINCT e.*, con.*, job.name AS job_name, dept.name AS dept_name FROM employees e LEFT JOIN contracts con ON e.id = con.employee_id LEFT JOIN departments dept ON con.department_id =

moctarss's avatar
moctarss's avatar moctarssCode Review
2

How to convert this sql query to Eloquent ORM.

SQL Query SELECT v.product_id FROM ( SELECT product_id, count(*) AS matches FROM product where filter_id IN (2,4) GROUP BY product_id ) AS v WHERE v.matches = 2 I want to convert this query to Eloquent ORM.

PJijin's avatar
PJijin's avatar jlrdwEloquent
2

MySql query to Laravel Eloquent conversion.

Helo I want to convert this query to laravel eloquent. Any suggestion how to do that? SELECT * FROM chat WHERE (id_participant1 = 1 OR id_participant2 = 1) AND (id_participant1 = 2 OR id_participant2 = 2)

mohammadbasir's avatar
mohammadbasir's avatar mohammadba...Eloquent
2

How to use Substr in Laravel query?

Hi, I want to use substr in laravel query, but I am getting error. Here's what I have tried so far- $items2 = Course::where('school_id','=',Auth::user()->school_id) ->where(\DB::raw('SUBSTR(name, LOCATE('-', name) + 1)')) ->where('status','=',1) ->get(); Even I tried query builder also- $items3 = \DB::table('courses') ->wher

abingpj's avatar
abingpj's avatar abingpjEloquent
2

DB::update query

I am trying to use the query using the valided values, $book_name, $isbn_no & $book_price and ofcourse the $Id but the query doesn't work. public function update(Request $request, $id) { $validatedData = $request->validate([ 'book_name' => 'required|max:255', 'isbn_no' => 'required|alpha_num', 'book_price' => 'required|numeric',

c3422867's avatar
c3422867's avatar c3422867General
11

Laravel raw query to Eloquent.

Hi, I have a query that I want to convert it into eloquent. \DB::select('select * FROM brands WHERE id in ( select brand_id from products WHERE category_id IN (220, 222, 223) GROUP by brand_id )'); Any tips?

abingpj's avatar
abingpj's avatar tisuchiEloquent
1

Raw SQL query and binding data?

create.blade.php @csrf First Name Last Name SAVE controller > Route::POST('/form-filled', 'PagesController@store'); & then my store function is: DB::insert('insert into projects (title, description) values (?, ?)', ['Dynamic' 'some stuff about stuff']); I can get my form values as follows in my store function: $title = request('title'); $description = r

c3422867's avatar
c3422867's avatar GamerFac3General
6

trying to run a raw query and getting a syntax error 1055

I have this code: $resultados = DB::select('SELECT smrt_preguntas.grupo_pregunta, SUM(smrt_respuestas.puntaje) AS valor, smrt_usuarios_pruebas.usuario_id FROM smrt_usuarios_pruebas_respuestas smrt_usuarios_pruebas_respuestas

dru's avatar
dru's avatar SinnbeckGeneral
3

Eloquent User Provider Causing Duplicate Query

I'm required to calculate the current 'rating' of a user (model) based on a relationship with Activity(model). I therefore have the following in my User model: protected $with = ['lastActivity']; protected $appends = ['rating']; public function lastActivity() { return $this->hasOne(Activity::class, 'user_id')->latest(); } public function getRatingAttribute() { $us

thebigk's avatar
thebigk's avatar SnapeyEloquent
4

Eloquent query to filter same column values

Hey I want to get the product_id from this table Table product_filter I want to get the product_id from this table. And the conditions are filter_id needs to be 4 AND 3. There may be multiple filter_id, the filter values need to exist. I can do this SQL query SELECT f1.product_id FROM product_filter AS f1 JOIN product_filter AS f2 ON f2.product_id = f1.product_id A

PJijin's avatar
PJijin's avatar HaeyzedEloquent
14

Use find() and with() together in Laravel query

I have 2 tables employees and employee_locations. One employee has many locations. I need to find out one employee record with related latest employee_locations record. I wrote below query. $employees = Employee::find([1])->with('employees.employee_locations')->latest()->first(); I am getting below error BadMethodCallException Method Illuminate\Database\Eloquent\Colle

afoysal's avatar
afoysal's avatar SinnbeckEloquent
10

Laravel groupBy query (API Resource)

Hi I am trying to get a student marks details that are group by exam_type. I need to create a API in below format. But I am unable to do that one. I need help with building query. { "success": true, "data": [ { "exam_type": "Annually", "details":[ { // all student

shariff's avatar
shariff's avatar matheenull...Laravel
17

Disable global query scope on specific controller

Greetings, I created a global query scope in one of my models. The scope is called "waiting", and it is used to indicate that a follow resource is waiting to be approved. protected static function boot() { static::addGlobalScope('waiting', function(Builder $builder) { $builder->where('waiting', false); }); } I separated the

pedroroccon's avatar
pedroroccon's avatar pedrorocco...Eloquent
3

Eloquent Query Just Not Working?

I'm totally dumbfounded by this... I have a table with two columns, user_action_id and correct_action_id. I just want to return all rows where user_action_id is EQUAL to correct_action_id. Why does this query not work: $correctQuestions = \App\Question::where('quiz_id', \Auth::user()->active_quiz_id)->where('user_action_id', 'correct_action_id')->get(); return $correc

TylerW's avatar
TylerW's avatar tykusEloquent
1

Laravel union query using Eloquent and Query Builder

This is my sample query that i want to implement in laravel Eloquent or Query Builder SELECT in_records.item_id, in_records.date, in_records.value AS 'IN', '' AS 'OUT', in_records.USER FROM in_records UNION SELECT out_records.item_id, out_records.date, '' AS 'IN', out_records.value AS 'OUT

kaizokupuffball's avatar
kaizokupuffball's avatar kaizokupuf...Laravel
3

Pagination issue with Search query

Hi, I want to add the pagination in my search query. But I a confused how to use pagination along with Join tables. Here is my query. $user = CareerSolution::where ( 'subject', 'LIKE', '%' . $q . '%' ) ->where('career_solutions.topic_category_id', '=', $c) ->join('role_users' , 'role_users.user_id', '=', 'career_solutions.user_id') -&

ronyamin's avatar
ronyamin's avatar jeffreyvan...Laravel
3

Can't see the complete query so I don't know what's wrong

I have the following function: public function second($degree_id) { $question = DB::table('questions') ->select('questions.question_text','questions.answer_time_minutes','questions.answer_time_seconds') ->join('universities', 'universities.university_id', 'questions.university_id') ->orderByRaw('RAND()') ->where('questions.degree_

ferba24's avatar
ferba24's avatar CronixLaravel
10

Convert MySql query to Laravel

Here my MySQL query (work in phpMyAdmin) : SELECT workcenter, (SUM(w1+w2 +w3 +w4)/ (COUNT(DISTINCT(teknisi))*40*4) )*100 AS total FROM `team` GROUP by workcenter ORDER BY total then, i try in Laravel Sintax like this below (not work) : $sql = Team::groupBy('workcenter')->select('workcenter', \DB::raw('(SUM(w1+w2+w3+w4)/ (COUNT(DISTINCT(teknisi))*30*4) )*100 AS total'))

nshahadat's avatar
nshahadat's avatar nshahadatEloquent
2

Can somebody help me to convert postgresql query to laravel query

This is my postgresql query SELECT elem FROM orders, json_array_elements(people) elem WHERE elem->>'position' = '1'; Thank you.

chalahed's avatar
chalahed's avatar chalahedLaravel
16

Help with sending emails Query

Hey all i need some help with a query for sending emails. I have a field in my users table 'last_login' in witch i am storing the date and time when the user was last logged in. i need to check if the user is not logged in for more than 2 days and send them an email reminder. any thoughts on how to do this? is there any Laravel helper for this kinds of scenarios?

dzalev's avatar
dzalev's avatar dzalevCode Review
7

How to query the last 12 months worth of data? Instead of every month this year.

I need to make a query of the last 12 months OF DATA and sums the readings for each month so that I can output this to a chart. This is what I currently use now: if (Utility_Meter_Readings::where('property_id', $property_id)->where('resource_type','1')->count() > 0) { $huffdash_energydata = []; $i=12; while ($i >= 0){

fbc's avatar
fbc's avatar TalinonLaravel
2

Eager loading where query returns empty array

Hi there, i am having a small issue with a where query on a relation. When ever i try to get $courses, it returns an empty array - however, if i dump the same query i'll receive the desired results. dd($query->where('businessunit', $queryParams['filterByBusinessUnitShortName'])->get()); Here is the controller where the query is being used. //CourseController.php $queryP

someUser's avatar
someUser's avatar goldtasteCode Review
3

How to cache a paginated query

i'm fetching data from DB like this public static function getArticleComments($arc_id) { $comments = Comment::with('user') ->where('article_id', '=' , $arc_id) ->orderBy('id', 'desc') ->paginate(10); return $comments;

nhayder's avatar
nhayder's avatar SnapeyLaravel
6

Calculate time difference in Eloquent Query

I have a Collection with the following structure; Collection {#6467 ▼ #items: array:7 [▼ 0 => Appointment {#6459 ▼ ... #attributes: array:9 [▼ "id" => 1092 "company_id" => 2 "employee_id" => 5 "client_id" => 31 "notes" => "Fugiat reprehenderit cumque

RafaelMunoznl's avatar
RafaelMunoznl's avatar RafaelMuno...Laravel
4

Using laravel-scout tntsearch along with eloquent query builder

Hi everybody, I want the user to sort, filter AND tnt-search Model entries, in my laravel backend I have a search method API call, currently I can't get scout's Model::search method to work along with Eloquent's query builder, so you either: Search by text if request has 'search'. Sort or filter with eloquent query builder (see link bellow for more info). How can I get both

Gabotronix's avatar
Gabotronix's avatar rodrigo.pe...General
1