Eloquent many to many query searching values from both tables. All values should be present.

I have a table with called “songs” which contains songs that have a “title”. I also have a table called “artists” which contains artists that have a “spotify_id”. A song can have many artist and an artist can blog to many songs so I have a pivot table “artist_song” which connects the “songs” and “artists” tables In my models I have... class Song extends Model { public funct

Kimmer's avatar
Kimmer's avatar darlenehaw...Eloquent
8

Between times query

I'm trying to figure out how to query a table in my database for all events that start in the morning so between midnight and say midday tried the following but not having much luck. Any ideas? $sessions->whereBetween('start_date',array(00:00,12:00));

maplerock's avatar
maplerock's avatar maplerockEloquent
3

Help with building a query to retrieve colors

I have the following db structure: products: id, product_id, name, desc, etc... categories: id, name, slug product_category: product_id, category_id product_colors: id, product_id, hex, name I also have the route: /category/{slug}, where a user sees all products under a category, with a sidebar for filtering them by price and other categories. What is the best way to retrieve

PaulDiamant's avatar
PaulDiamant's avatar PaulDiaman...Eloquent
7

query orwhere creating problem

here is my query $count = DB::table('processed_applicants') ->whereBetween('accept',[0,2]) ->where('expiry','>=',carbon::now()) ->whereNull('expiry') ->where('jobpostingid',$id) ->take(9) ->get(); suppose i have id (example)=j100,j1005 of table job

bipin's avatar
bipin's avatar tykusLaravel
1

Return query with relationship results to view

Hey all, just getting my hands dirty with laravel and something has me stumped which should seem pretty simple but even after watching 5 Fundamentals, 5.4 From Scratch and Build a Forum with TDD and Laravel I cannot for the life of me pull off the simple task of sending the results of my query to my controller/view. I have a users, projects and a pivot table, project_user. The

kevbrn's avatar
kevbrn's avatar bobbybouwm...Eloquent
3

How do I access alias inside multiple select in Db query (closed)

Hello, guys, I have a condition to make inside my query builder. But the problem is how do I access alias that is inside multiple select. $jobs=DB::table('jobs') ->select(DB::raw('jobs.id as id,category.name as cat_name,jobs.name as job_name,jobs.pay_amount,jobs.pay_type,jobs.date_start,jobs.date_end,jobs.time_start,jobs.time_end,location_text,company.company_logo,color,co

syamimhazmi's avatar
syamimhazmi's avatar syamimhazm...Laravel
1

Convert MySQL query to eloquent or query builder

MySQL query select calendars.datefield AS date, ifnull(sum(sales.sale_total),0) as total from sales right join calendars on (date(sales.created_at) = calendars.datefield) where (calendars.datefield between (select min(date(created_at)) from sales) and (select max(date(created_at)) from sales)) group by date I'm trying like this... DB::table('sales') ->rightJoin('calendars',

Warar's avatar
Warar's avatar daisywalke...Eloquent
9

Query by date range in db date range fields

In my application I can add holidays for employers, setting date range (start_date, end_date). In the frontend user can choose two dates- "date_from" and "date_to" and I should perform query which should return holidays for which at least one day of holiday is between that given dates (so return not only holidays that fits enitirely in that date- which I kno

wizjo's avatar
wizjo's avatar wizjoEloquent
3

How to use JOIN query with LEFT() SQL function and query builder

Hi there. I'm having a problem with query builder using MSSQL and custom JOINs. It won't accept a SQL LEFT method as one of join conditions. Here is the case: I'm trying to construct a query with my Eloquent model which would look something like this. SELECT [List].[LIST_ID] [ListParam].[ADDR_ID], [Addr].[ADMIN_CODE]. [Region].[NAME] FROM List INNER JOIN [ListParam] ON [L

nedo's avatar
nedo's avatar nedoEloquent
2

Eloguent query replacement for my raw query

What can be the best replacement if I use modal relationships for below query and what is the disadvantage if I use below. $games = DB::table('xx') ->join('yy', 'xx.yy_id', '=', 'yy.id') ->where('xx.search_id','=',$id) ->join('uu', 'yy.id', '=', 'uu.yy_id') ->join('zz', 'uu.id', '=', 'zz.uu_id') ->select('zz.*','uu.name as category') ->get();

RamjithAp's avatar
RamjithAp's avatar SnapeyEloquent
1

Constraining main laravel query by its eager-loaded relationship

I have an eloquent model (Tenancy) which has a one-to-many relationship with a model 'RentPeriod', and I am trying to perform (with Eloquent) the equivalent of a left join to return a single record (and single field) from the 'RentPeriods' table, with which I can then filter the outer query. Basically, the RentPeriods table has a "due_date" field, which I would like t

BennyWalden's avatar
BennyWalden's avatar jlrdwLaravel
5

Constraining relationship query by an array

I have 3 models Content, ContentType, and Taxonomy. Their relationships are set up like this: Content model: public function contentType() { return $this->belongsTo('App\ContentType', 'ct_id'); } public function taxonomies() { return $this->morphToMany('App\Taxonomy', 'taxonomical'); } ContentType model: public function contents() { return $this->hasMany('

Leff7's avatar
Leff7's avatar ftrilloEloquent
1

Where to put a custom query builder?

Hi All, I'm fairly new to modern development coming from a rather simplistic mvc framework. While porting bits and pieces I wonder where to add my custom google analytics query builder. I see Laravel has a "Concerns" folder for its Eloquent querybuilder. I have initialised the googleapi in App\Services and use App\Repositories to get certain data sets where the fluent

artcore's avatar
artcore's avatar artcoreGeneral
0

Laravel Stored Procedure Vs Inline query

Inline query Vs Stored Procedure in Laravel 5.4 which one is best for use?? Give me the reasons please. Can we use both?

VijayInfoPixel's avatar
VijayInfoPixel's avatar shez1983Laravel
2

How to echo single json object after a query

Hi, Quick question please. I have a query like this one in my user model public function getRole(){ $role = DB::table('users') ->join('roles', 'users.role_id', '=', 'roles.id') ->select('roles.name') ->get(); return $role; } When I return it to my home Controller public function index(Request $request) { $loggedInRol

LaraBABA's avatar
LaraBABA's avatar tykusLaravel
10

Using normal MySQL instead of the query builder in Laravel

Hi, I am the next one ;-), who is going over form CI to Laravel. Looks very nice at first sight. DId some lessons "Laravel from scratch" and it looks more advanced then CI. My anxiety with CI is that it stops in the near future......... I believe that using the right language with the right method is the best, so my question is; Is it possible (and smart) to use the m

larabella's avatar
larabella's avatar larabellaLaravel
4

Eloquent vs query builder, why?

Hello, I am not sure I yet understand why people use Eloquent and why others use the query builder. Apart that Eloquent seems easier to write, is there any major difference between the 2 please? I would like to process database transactions, I presume that in both eloquent and the query builder this can be done. I would really like to hear from someone who has been using both f

LaraBABA's avatar
LaraBABA's avatar TriumfatorLaravel
9

[Help] Create Loop Search Query

hello, I really need help for build my search query, my code is like this // example keyword : Leonardo+Da+Vinci $keyword = str_replace('+', ' ', $request->keyword); $db = Book::where('title', 'like', "%$keyword%") ->take(10) ->get(); return $db; how to set search like this $db = Book::where('title', 'like', '%Leo

minions's avatar
minions's avatar minionsLaravel
2

How to manually query the soft deleted data

The model is using SoftDeletes trait. Does anyone know how to manually query the deleted files? I wanted to query the deleted data without using the withTrashed() or onlyTrashed() functions

skeith22's avatar
skeith22's avatar ftrilloEloquent
1

Query speed problems, one much slower than the other.

Hi I'm getting a problem where two seemely similar queries are running at completly different speeds, even though the relationships data I'm querying is much smaller for the slow query. Order::has('buyers')->limit(10)->get(); // 16 seconds Order::has('packages')->limit(10)->get(); // 0.5 seconds Below I've included the raw query that's generated and everything I kn

robgoodliffe's avatar
robgoodliffe's avatar robgoodlif...Eloquent
4

Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query

Hi guys, I need to save a string into column image type to MSSQL but i recieve this error: Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. code below: $orcamento->ZCO_OBS = 'Test 123'; $orcamento->Save(); Can you help-me?

Davkruger's avatar
Davkruger's avatar martlebyLaravel
5

How to query pivot table where clause?

Hi everyone, I have 3 tables (users, departments, department_user), I can query the pivot table results fine on my returned views but I am trying to write an eloquent query with where clauses on the pivot table. My pivot table has a column called "term_end_date" and I am trying to get all records prior to a certain date. Any suggestions on how I would do this? $depart

mchiasson's avatar
mchiasson's avatar mchiassonEloquent
2

Users role check, repeated query issue.

Hi, I am developing an application which is Role based system. So I am trying to know what is the Laravel way to deal with following problem: I've been using this method of checking for role Auth::user()->hasRole('admin'), throughout my layout and view files. It was only until I installed Laravel Debugbar that I realise there were multiple repeated database queries. For inst

arifkhn46's avatar
arifkhn46's avatar DevonLaravel
1

Convert SQL query to Eloquent [Solved]

I need help to convert a complex SQL query into eloquent. I have models for all tables (search & airports) in order to join them with With(). The query is: "SELECT *, fromTable.iata AS from_airport_iata, toTable.iata AS to_airport_iata FROM ( SELECT * FROM searches ORDER BY searches.id DESC) AS tmp_table LEFT JOIN airports fromTable ON fromTable.id = tmp_table.from_

Dieuz's avatar
Dieuz's avatar DieuzEloquent
2

How to make sub query where in laravel

this is my query SELECT p.id_pekerjaan, p.nama as pekerjaans, ks.tgl_survey FROM users u INNER JOIN kegiatan_surveys ks on(u.id = ks.user_surveyor_id) inner join pekerjaans p on (ks.pekerjaan_id = p.id_pekerjaan) inner join kontraks k on(k.pekerjaan_id=p.id_pekerjaan) inner JOIN setting_basts sb on (sb.kontrak_id=k.id_kontrak) WHERE ks.tgl_survey > (SELECT ks.tgl_survey

hecate0211's avatar
hecate0211's avatar CronixLaravel
6

Query on a table with 1 million rows

So I have a table in my database that has about a million race results. Out of the million, there are about 250,000 unique names (people have results for multiple races). I'd like users to be able to quickly search for a name in the database. Obviously querying the results table isn't ideal. So I was thinking of a nightly job that pulled all distinct names from the results tabl

Corys8646's avatar
Corys8646's avatar DefragGeneral
1

Run eloquent query from another local virtual host with curl or api

I have 2 virtual host in my wamp server under Windows 10(X64). Virtual-host1 and Virtual-host2. virtual-host1 pointed to www/virtual1 and virtual-host2 pointed to www/virtual2 both virtual host running with Laravel 5.5. Virtual-host1 have a configured database: virtualhost1_database Virtual-host2 does not have any database, default is: homestead I defined a resource route in Vi

mostafa.miri65's avatar
mostafa.miri65's avatar mostafa.mi...Laravel
1

Best way of performing eloquent query when request exists and has a relationship matching the request.

Hi guys, I am trying to perform a query when a request exists but its quite slow to load so wanted to know if there was a better and quicker way of doing it. $users = Travellers::select([ 'id', 'firstname', 'surname', 'updated_at' ])->when($request->has('school'), function ($query) use ($request) { $query->has('passes.school')->whereHas('passes', function ($query

nizam0786's avatar
nizam0786's avatar nizam0786Eloquent
1

Laravel db::raw query for the query scope.

Hi I have a query scope in laravel and I have a column named km which is varchar. Now I want to write a raw query scope for it. The problem is it wont search for the varchar. So need to cast km to unsigned. Product::km($kmFrom, $kmTo); public function scopeKm($query, $kmFrom, $kmTo) { $kmFrom = (int)$kmFrom; $kmFrom = (int)$kmTo; if((count($

eddy1992's avatar
eddy1992's avatar 36864Laravel
3

Need help with Eloquent data model/relationships or query statement

I'm trying to get all reservation times for all accounts belonging to the authenticated user. Here are the models: users: id, name account_user (pivot): account_id, user_id accounts: id, accountname account_schedule (pivot): account_id, schedule_id schedules: id, accountname, reservationtime Relationships: users has many accounts accounts has many schedules I have tried both

stephen_p's avatar
stephen_p's avatar stephen_pEloquent
5

Intersection between two query result

i have two query result if($request->has('city')) { $data1=$this->vehicle->searchVehiclesByGeneralFilter('cities',$request->city); } if($request->has('engine_type')) { $data2=$this->vehicle->searchVehiclesByGeneralFilter('engine_types',$request->engine_type); } i want only common rows in these two arrays $data1 ,$data2

NadeemAkhter's avatar
NadeemAkhter's avatar NadeemAkht...Laravel
10

How to add a select query to builder ?

How can i add a select query to a method? I tried adding a query to the zipcode method but it failed and returned an error namespace App; use Illuminate\Database\Eloquent\Builder; class InventoryFilters extends QueryFilters { public function years($years) { $years = explode(',',$years); return $this->builder->whereIn('year', $years); }

willthinkk's avatar
willthinkk's avatar willthinkkEloquent
5

Laravel 4.2 Query to Database

Hello, I would like to know how to write this query in Laravel. SELECT * FROM ( SELECT ce.empresa_id, CONCAT(ce.nombre, ' ', ce.apellido) AS nombre, ce.grupo_contable FROM cliente_empresa AS ce UNION SELECT cc.empresa_id, cc.nombre, cc.grupo_contable FROM cuenta_contable AS cc UNION SELECT cci.empresa_id, cci.grupo_iva AS nombre, cci.cuenta_contable AS grupo_contable FROM cuent

DanielShine's avatar
DanielShine's avatar cre3zLaravel
7

PHPUnit fails to count with group by on query to the database view (not table)

Laravel Version: 5.4.* PHP Version: >=5.6.4 Database Driver & Version: MySQL (Testing with SqlLite) Description: I have an Eloquent model called ProductStatistics with $table='view_products', here view_products is a database view rather than table. view_products has following: | product_id | product_name | category_id | category_name | | ------------- |:-------------:|

rcadhikari's avatar
rcadhikari's avatar rcadhikariTesting
0

query conversion manual

I have a few rows of a query which I run in MSSQL but I want to try converting the query into the procedure of laravel. anyone can help me? general query : select NM_PERUSAHAAN, count(*) as total_count, sum(FLAG_TERIMA) as approved, sum(1 - FLAG_TERIMA) as not_approved from MSTBEASISWAS group by NM_PERUSAHAAN;

Rahmah's avatar
Rahmah's avatar tykusLaravel
1

Access query "wheres"

Title is vague, exuse me for that. Is there a way to get the current id you're looping through? As you can see in the example below, I need the post id from a couple callbacks before. Anyone knows if this is possible? $collections = Collection::with(['posts' => function ($q) { // i need current id from post where the query is looping through $id = $q; $q-&g

dev.kobus's avatar
dev.kobus's avatar dev.kobusEloquent
0

How to get fast response from Laravel4 query builder

I'm using laravel4.2 and I've used some queries to get the response from MySQL tables. I have around 80k records in specific table sometimes it could be more. I want to know is there any way to get an instant response? I only want to use query builder not eloquent. Thanks in advance laracasts people ;)

Dhaval_patel's avatar
Dhaval_patel's avatar Dhaval_pat...Laravel
2

Query builder problem

Hi, i have a problem with the query builder that's quite odd. I have this two queries: $tmp2 = Temp2::where('parent',54125)->get(); $tmp3 = Temp2::where('parent','54125-G')->get(); I get the same collection as result. I really cannot understand why. If i cast 54125 as string the query works properly. And i get an empty collection. The field 'parent' is VARCHAR(20). In th

fabio571's avatar
fabio571's avatar rebirthtob...Laravel
1

Advanced query in the Query Builder

Hi, How can i do the following query from the Laravel Query builder ? SELECT * FROM `my_table` WHERE `col1` = 'value1' AND `cold2` = 'value2' AND NOT ( `col3` = 'value3' AND `col4` = 'value4' AND `col5` = 'value5' ) Thank you in advance.

DimiFFT's avatar
DimiFFT's avatar DimiFFTEloquent
2

Help with SQL Query

Hello, I have the following code and it keeps giving error, if anyone knows of a possible solution I will be glad. $query = DB::table('patients'); // $query->select(DB::raw("COUNT(`id`) as count, DATE_FORMAT(`created_at`, %M) as date")); $query->select(DB::raw("DATE_FORMAT(`created_at`, %M)as date, COUNT(`id`) as count")); // Preferable, s

qasimbotani's avatar
qasimbotani's avatar qasimbotan...Eloquent
2

how to update first row if have multiple results in query (over laravel 5.3)

I want to update the first row only if the select query return multiple data. Example code : under controller : public function update(Request $request, $idOrEmail) { return Sample::idOrEmail($idOrEmail)->firstOrFail()->update($request->validated()); } Under App folder public function scopeidOrEmail($query, $id_or_email) { if (! is_numeric($id_or_email) && filt

wilsonlee's avatar
wilsonlee's avatar wilsonleeLaravel
0

MaatWeb/Excel +Laracast "Eloquent Techniques: Dedicated Query String Filtering"

After completing Laracast "Eloquent Techniques: Dedicated Query String Filtering" then trying to export that filtered data with Maatweb/Excel I get these errors when exporting (2/2) ErrorException Undefined variable: events (View: /var/www/html/graphs/resources/views/traces/sortable.blade.php) and (1/2) ErrorException Undefined variable: events in 155602786d9591ddb8b1

jngwny's avatar
jngwny's avatar jngwnyLaravel
2

combine (like + in) inside where query

I have a posts table (Post model), I want to search for a string like this (Laravel query builder), and I want to search to run like the following: 1- explode search string to create array $keys = ['Laravel', 'query', 'builder']; 2- search if any item in the array is found in post title or post body $posts = Post::where('title', 'in', $keys)->orWhere('body', 'in', $keys)-&g

AbdallahSabri's avatar
AbdallahSabri's avatar daanbEloquent
1

Formatting query builder output in laravel

Hello, I made a query which looks like this: $hotel = App\Hotel::where('hotel_id', 2) ->join('rooms', 'rooms.hotel_id', '=', 'hotels.id') ->join('cities', 'hotels.city_id', '=', 'cities.id') ->select('hotels.name as hotel_name', 'cities.name as city_name', 'rooms.name as room_name') ->get(); Which outputs: Illuminate

nutellaJ's avatar
nutellaJ's avatar SnapeyLaravel
1

Parameter randomly being added to SQL query?

I'm trying to run the following query: CompetitionMatch::where('competition_id', $competition->id)->where('id', $match->id)->update(['posted_at' => Carbon::now()]); But I get the following error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens (SQL: update `competition_matches` set `posted_at` = 2017-09-10 22:

Samer_J's avatar
Samer_J's avatar Samer_JLaravel
8

string delimiters missing in insert query

Hallo, I've installed laravel 5.5, php7.0.23, mysql5.5.53 and a first application with auth. When I try to register a user there is this error: (2/2) QueryException SQLSTATE[HY000]: General error: 1364 Field 'first_name' doesn't have a default value (SQL: insert into sos_users (name, email, password, updated_at, created_at) values (alonso, [email protected], $2y$10$XPCBYlJAp

maan's avatar
maan's avatar robman70General
6

Translate Laravel DB::raw() query with join to Eloquent

hello every one please can some one help me with the equivalent of this query in Eloquent? I'm using Laravel 5.4 SELECT stock_id, prod_id, prix , priority FROM stock as stock INNER JOIN (SELECT * FROM products WHERE sold=false AND brand_id='brand_id') as product ON stock.stock_id = product.id Order By stock.priority;

pafait's avatar
pafait's avatar pafaitEloquent
0

Dedicated Query String Filtering

I am attempting to create a filter search function from https://laracasts.com/series/eloquent-techniques/episodes/4. I am able to filter names, but I have difficulties in querying many to many relationship here as it uses 'builder' and 'class'. how do I filter my 'maincategories' here: public function name($search) // company?name { return $this->builder->wher

msyaukat's avatar
msyaukat's avatar msyaukatLaravel
0

How to Apply Same Query to Multiple Eloquent

Hi Guys, I have 4 tables product, customers, salesmen and invoice And i am doing some filter like Include customer, Include Salesmen Include customer, Exclude Salesmen In my case the query is same all time but i need to apply it to different Eloquent every time.(Product Model with Invoice table, Customers Models with Invoice Table, Salesmen Model with Invoice Table) Is there an

vinodjoshi's avatar
vinodjoshi's avatar JayteeEloquent
4

How to post query to different controllers in Laravel 5.4?

I am new to Laravel and wondering if there is any method or ways to post the query to different controllers (or functions) i.e 2 or more from a laravel form. I am using Laravel 5.4 and I want to extract data from multiple tables(3 in my case), then return view to different pages or different pills/tab on a page. My controller looks like: class QueriesController extends Controll

Sanathoi's avatar
Sanathoi's avatar SanathoiRequests
0