How to exclude a specific column from query when using query builder?

I am using query builder to get my data from database. $result = DB::table('test')->.......... Instead of explicitly select all the column, is there a better way to select every column except for certain column?

Crazylife's avatar
Crazylife's avatar SairahcazEloquent
3

“like” operatior and laravel SQL injection in join raw query

up vote 0 down vote favorite In laravel if i have a model FORM and table cars, is it safe to use raw query like this: Form::crossJoin(DB::raw('(SELECT * FROM cars WHERE cars.title like "%somewords%")cars'))->get(); In this case i used like operator for search. Is it vulnerable for sql injection? If yes, how make it safe?

esmaeil1's avatar
esmaeil1's avatar esmaeil1Laravel
4

Error on dedicated query string after Homestead update (PHP 7.2 ask for Countable implementation)

Hi, I followed the tutorial on Laracasts to make a dedicated query string filter for Eloquent. (https://laracasts.com/series/eloquent-techniques/episodes/4) Everything was working perfectly, since I upgraded Homestead to a new version (especially a new version of PHP 7.2). The version of the Laravel framework I did use is 5.2. I'm planning to upgrade it to the latest 5.5 but he

dib258's avatar
dib258's avatar dib258PHP
1

Eloquent Query is Pulling in a Ton of Bogus Records

The query is: $fragments = Fragment::where('seed_id', $seed_id)->orderBy('position', 'ASC')->get(); When I go and actually check the database the correct number of records are present (4). When the above query is executed it returns 40+, and isn't consistent, it's returned as many as 70. I outputted the data, and the new "bogus" entries are the original 4 with t

MrThunder's avatar
MrThunder's avatar bobbybouwm...Eloquent
3

Complex DB query

I had to write a complex query for a project long ago. At the time it was written using Drupal. I'm trying to figure out how to write this in Laravel. I have resorted to writing it using DB::raw(), but would really like to know how to write it using Laravel. It's go so much that I get lost when trying to group parameters. Can someone show me how the following would be written i

lara68236's avatar
lara68236's avatar sutherlandLaravel
6

Clone and chain issue in laravel query builder and queues

Please consider my class constructor : public function __construct(User $query = null) { $this->query = $query ?: (new User())->getQuery(); } I have a method like this : public function getNullActivityUsers() { $query = clone $this->query; $query->whereNull('activity'); return $query->get(); } When I call this method, the sql query will be thi

misagh's avatar
misagh's avatar misaghEloquent
1

Auth User in Query, is this also good practise?

Hi guys, I have a quick question. is this a good practise? $test = WorkersTest::where('workers_id', Auth::user()->id)->get(); basically this is a query where is only select based on their login auth::user is this a good practise? Thank you

Edgy's avatar
Edgy's avatar EdgyLaravel
2

query no result, but there are results if directly use SQL statement

$query is as follows: $query = Policy ::join('contracts as c', 'c.id', '=', 'policies.contract_id') ->join('market_partners as mp', 'mp.id', '=', 'c.market_partner_id') ->join('markets as m', 'm.id', '=', 'mp.market_id') ->join('policy_versions as pvall', 'pvall.policy_id', '=', 'policies.id') ->where('m.country_code', '='

rose's avatar
rose's avatar roseLaravel
2

Laravel - No query results for model When Using An Array Passed Via Ajax

I am getting the error; No query results for model [App\UserProfile] 0 When I use findOrFail using an array passed with AJAX in a foreach loop. If I manually create the array in PHP, the entity is found using the array values. When I do a print_r() on the array in PHP, the array is present in both the AJAX array and the manually created array. AJAX Code function checkChatOnli

temwa's avatar
temwa's avatar temwaLaravel
21

Query vs ForEach Loop, Which one has the better performance?

Hi folks, I'm wondering between Query vs Foreach loop, Which one has the better performance? Here is a simple example, my web app, has a reply table and like table, the reply and like has onetomany relationship, a reply can have many likes and a like belong to a reply and in this function I need to get a like record/model that is liked by current auth user 1, Using query public

zoldic's avatar
zoldic's avatar zoldicGeneral
9

How to query this

I am working in a project.for now lets say i have 3 tables "districts", "crimes" , "cases". Cases table holds all the registered cases. Case.php class Case extends Model { protected $table = 'cases'; protected $fillable=['person_name','crime_id ,'district_id','crime_date']; public function crime() { return $

david001's avatar
david001's avatar david001Eloquent
4

how to query the right way.

Hi, i have a Model Room and a Model reservation. A room hasmany reservations. Now i have to Dates, a start and a Ende Date. Now my question. How can i get all reservations of the room on given dates? In my bladefile something like this ... $room->hasReservation( $start, $end) Right now i do some kind of query on the room Model, but somehow i always get all reservations. Not

uhorn's avatar
uhorn's avatar uhornLaravel
3

Eloquent query in laravel 5.5

I have written the following query to fetch records from database $query4 = DB::table('rbac_access_rights')->select('rbac_roles.mr_role_id', 'rbac_roles.mr_role_nm', 'rbac_roles.mr_user','rbac_forms.fm_form_id','rbac_forms.fm_form_nm','rbac_forms.fm_form_type', 'rbac_modules.mm_module_id','rbac_modules.mm_module_nm', 'rbac_access_rights.mar_r

anp24's avatar
anp24's avatar tykusLaravel
13

New on laravel (doing a query with groupby and count)

Snippet in the controller $numberoftwits = DB::table('twits') ->groupBy('user_id') ->count() ->get(); mysql query SELECT COUNT(*) FROM twits GROUP BY user_id Error Call to a member function get() on integer What should I do in order to pass the result of the query to the compact statement in the controller?

ramher101's avatar
ramher101's avatar abdelrhman...Laravel
8

advanced query grouping

Hi all, this is my first question on laracasts, I hope I'm doing it right :-) what I want: query my DB to get the lastest featured news from 3 different topics what I did so far: News::select(DB::raw('*, MAX(published_at)')) ->where('featured', 1) ->where('state', 'published') ->where('published_at', '<=', Carbon::now()) ->groupBy(DB::raw('primary_to

kantholy's avatar
kantholy's avatar burlresear...Eloquent
1

Query builder, return where all in

I will try one more time with this question, but now in new discussion instead of using already opened question. So, I have tables customers, products and customer_products. In customer_products there are id, customer_id and product_id columns. In search form I have 'products' input field, and can add multiple products for searching. So, in that case $request->products is ar

kova's avatar
kova's avatar kovaLaravel
2

Filter MongoDB results dynamically with where() and URL query string

Hello forum members, right now I'm trying to implement a restful API where users can filter a resource dynamically with the key value pairs in the url query string. All data are stored in MongoDB and are queried using laravel-mongodb. Let's suppose I've a resource called Persons where I would like to optionally filter by name and age. The url would look like this: www.example.c

codingislife's avatar
codingislife's avatar codingisli...Lumen
0

Laravel Eloquent Query has issues, while as raw query works fine

I am writing a query in Laravel 5.5 Eloquent with for which toSql() returns SELECT `entity_type`, `entity_id`, SUM(amount) AS total FROM `account_journals` WHERE EXISTS( SELECT * FROM `account_ledgers` WHERE `account_journals`.`ledger_id` = `account_ledgers`.`id` AND `name` = ? AND

ehsanquddusi's avatar
ehsanquddusi's avatar ehsanquddu...Eloquent
2

Get UserId with UserName without query

Hi guys; Do you guys know how to Get UserId with UserName without query? The regular way with query in the controller file public function testing($name) { $userId = User::where('name', $name)->first()->id; } is there any better way to get the id without the query? Thanks

zoldic's avatar
zoldic's avatar tykusLaravel
8

How to write this join using query builder

I have the below query I am trying to write using query builder. SELECT c.class_name FROM jobs as j INNER JOIN checks as c on (c.clientD = j.clientID and c.subID = j.subID and c.start_visit_num <= j.visitnum) or (c.clientID = j.clientID and c.subID = 0 and c.start_visit_num <= j.visitnum) WHERE j.jobid = ? and c.deleted = ? ORDER BY c.subID desc, c.clie

coxy121's avatar
coxy121's avatar coxy121Eloquent
3

Change mysql query to laravel 5.4

I have a mysql query SELECT * FROM Appraisal_skills WHERE INSTR(`assigned_to`, 6); Which working well.I want to change this to laravel I tried $getskillset=Db::table('Appraisal_skills')->where(`assigned_to`, 6)->instr()->get(); But got an error like BadMethodCallException in Builder.php line 2508: Call to undefined method Illuminate\Database\Query\Builder::instr()

shanukk's avatar
shanukk's avatar shanukkLaravel
5

Eloquent query filter with custom raw select

I wrote this eloquent query which works without an active filter: User controller: $users = User::filter(request(['ngf_id', 'first_name', 'last_name', 'tariff'])) ->select('*', DB::raw('YEAR(CURDATE()) - YEAR(birthdate) - IF(STR_TO_DATE(CONCAT(YEAR(CURDATE()), \'-\', MONTH(birthdate), \'-\', DAY(birthdate)) ,\'%Y-%c-%e\') > CURDATE(), 1, 0) AS age'))

basvandertogt's avatar
basvandertogt's avatar CinekLaravel
1

need help with building a sql query

Hi guys! Im struggling with little bit complicated sql query: public function isInHaplotype ('phenotype','10', $rs_ID_needle,'In Panel') { $getHaplotypes = $ghcCore->table('haplotypes')->select('id', 'rs_id_1', 'rs_id_2', 'rs_id_

arecuk's avatar
arecuk's avatar arecukLaravel
2

Query Builder for Date AND Time comparison?

Hi, I have a query such as return $query->whereDate('start', '<', Carbon::now()) ->orderBy('starts', 'desc') ->limit($limit); Obviously this is just ignoring 'times' - the 'start' is a Database carbon dateTime field. Does anybody know how If there is anything for including the time in the query? I know there is a whereTime() - but obviously it will still return wron

davestar057's avatar
davestar057's avatar usamajamil...Laravel
2

Eloquent Relationship or Query Builder for 3 relational tables?

Hi guys, I have 3 tables namely, workers_messages jobs and employers under workers_message have jobs_idcolumn. while jobs have employers_id . I am creating like a inbox page, so I am the employer and I will get the records from workers_message. The relation is simple: workers_message.jobs_id -> jobs.employers_id -> employers My question is, it is recommended to code thi

Edgy's avatar
Edgy's avatar Dry7Laravel
1

SQL to Eloquent -- How to convert this query?

Hello! I have this query that works very well: $sql = DB::select(" SELECT DATE_FORMAT(visits.dt, '%m') AS month_idx, DATE_FORMAT(visits.dt, '%b') AS month, DATE_FORMAT(visits.dt, '%Y') AS year, ROUND(sum(product_visit.amount)) AS y FROM visits, product_visit WHERE visits.id = produ

sergionader's avatar
sergionader's avatar sergionade...Eloquent
2

Laravel +postgresql groupby query

I have Model Called "Package". I want to use "SELECT * FROM package WHERE type='domestic' GROUP BY state" query with Laravel postgresql . How can i write this query using "Package" Model.

imtiyaz004's avatar
imtiyaz004's avatar Dry7Laravel
1

Improving Query

$data = TopWireless::select(DB::raw('UserName, sum(AcctInputOctets) as Input , sum(AcctOutputOctets) as Output')) ->whereDate('AcctStartTime' , 'regexp', date($data['date']))->groupBy('UserName')->orderBy('Output', 'desc')->limit(20)->get(); I'm wondering how to improve this query. This is taking too much time to complete.

imposition's avatar
imposition's avatar bobbybouwm...Eloquent
3

sub query in laravel

how to write sub query in laravel

saad_at2003's avatar
saad_at2003's avatar saad_at200...Laravel
1

No query results for model [App\Modules\Seo\Models\Seo].

I had to move my laravel files to a new computer and now my admin side doesn't want to show up. I get this error (2/2) NotFoundHttpException No query results for model [App\Modules\Seo\Models\Seo]. My admin file isn't calling the seo modules at all. If I go to my old computer I can still access the admin side. I tried composer dump-autoload, composer install and composer upd

Shiva's avatar
Shiva's avatar ShivaLaravel
2

ProductController getProduct() Method (Query) Problem

Hey, Currently working on the correct routing for products, but facing some problems with the Controller as through ´->get()->first();´ only the first Product of every business will be shown following this route: href="/products/{{ $product->business_id }}/{{ $product->id }}" Eloquent: Business hasMany Product Products Table: 'id, business_id' ProductCont

kendrick's avatar
kendrick's avatar splendidke...Laravel
4

Query: search products where subproducts has a value

Hello everyone, hope for little hint how to do next. I have $products and related $subproducts, relationship (product has many subproducts) is in place. What is correct way to find products where subproducts has some value. $value = '00-100'; $products = Product::join('subproducts', 'products.id', '=', 'subproducts.product_id') ->where(('subproducts.article_c

vladshoob's avatar
vladshoob's avatar vladshoobEloquent
3

Adding data to a model in a query scope

I'm sure I'm going about this all the wrong way so happy to be told the right way! I have two model User and Address. One to Many. I have a query that returns me all Users in a certain area by finding all Users attached to an Address in that area. Great. Now I want those results ordered by the nearest first. Obviously the User and the Address don't have a convenient ->distan

bwrigley's avatar
bwrigley's avatar bwrigleyEloquent
8

Eloquent or query builder?

Which should I go with if I build a landing website. It also has video and the content need to be dynamic. I think the wordpress is best for this the customer don't want that because he think the security is bad. There was a man asking me to improve his site but I fail to import his postgress. When I take a look at his laravel project, I notice that the man use 0 eloquent, no M

GTHell's avatar
GTHell's avatar GTHellEloquent
2

How to make this query in Eloquent

I have this query, it is working on oracle DB, How can I convert it to eloquent equivalent? select count(v.ID), v.SITE_NAME, to_char(v.REGISTRY_DATE,'yyyy') from abc.vehicles_v v where to_number(to_char(v.REGISTRY_DATE,'yyyy')) > 2012 and v.LAST_CANCEL_ID is null group by v.SITE_NAME,to_char(v.REGISTRY_DATE,'yyyy') order by 2

AbdallahSabri's avatar
AbdallahSabri's avatar AbdallahSa...Eloquent
4

Query on my model taking too much time to finish.

Hi there! I have this query inside my model: $data = TopFiber::select(DB::raw('UserName, sum(AcctInputOctets) as Input , sum(AcctOutputOctets) as Output')) ->where('AcctStartTime' , 'LIKE', $data['date'] . '%')->groupBy('UserName')->orderBy('Input', 'desc')->limit(20)->get(); But this is taking too much time like 5 minutes to comp

imposition's avatar
imposition's avatar impositionLaravel
9

Is there a way to assign the id to a different column when using firstOrNew in Eloquent without having to do a second query?

I have 2 questions but they're related. My table columns in my MySQL database looks like this: 'id', 'topic', 'root_id', 'parent_id', 'username', 'comment', 'created_at', 'updated_at' My query looks like this: $c = Comments::firstOrNew([ 'topic' => $topic, 'root_id' => $id, // <---- the auto increment id would go here 'parent_id' => $parent_id, 'use

rotaercz's avatar
rotaercz's avatar rotaerczLaravel
2

Sequential Database Query

Not sure if this is possible, but I think if it was that it would be a nice little hook. To call a save() or update() then do something all in one line. $profile->photo()->where('avatar', true)->update([$field => false]); $profile->photo()->where('id', '=', $id)->update([$field => true, 'published' => true]); So for example right now I have to call t

movepixels's avatar
movepixels's avatar movepixelsLaravel
2

Query Builder: how to retrieve records for every date in range, even if data is not there for some dates

If I have table Table with the following structure: Schema::create('Table', function (Blueprint $table) { $table->increments('id'); $table->date('date'); $table->integer('sales')->default(0); I am able to query it and retrieve data for a range of dates like this: // assume $startDate and $endDate exist and are valid

eloperdev's avatar
eloperdev's avatar rahulwalgu...Laravel
6

Filtering query results based on permissions

Hello, I would like to know what is the best approach to filtering eloquent query results based on user permissions. Let's say I have Orders with many possible statuses including open and ready and many user roles including admin and operator. On the same view I need to display filtered orders based on user role. E.g. admin will see all of them, operator will only see open orde

Arasma's avatar
Arasma's avatar SnapeyLaravel
2

raw SQL to laravel query

I need a little help with DB builder for an SQL server query statement. The question is here on stackoverflow https://stackoverflow.com/questions/47307871/raw-sql-to-laravel-query

wdydev's avatar
wdydev's avatar gregrobsonLaravel
1

Convert mysql query to laravel ORM query

I want convert mysql query to laravel ORM query, anyone can help... Here the Sql Query... query('SELECT PAYMENT, TOTAL, TRANSID FROM PAYMENTS WHERE RECEIPT = (select ID from tickets WHERE TICKETID = ?)'

francisMS's avatar
francisMS's avatar muthusarav...Laravel
7

How to allow user to do a search which returns a route with the search query in it?

Hi All! I have a search box that allows users to input a book name and then gives them a list of books that match that query. What I want to be able to do is after the search form is submitted it will direct them to a route: search/book/{searchQuery} How could I do this? Furthermore, what if the user searches something that is multiple words? Or special characters? Won't this b

jpeterson579's avatar
jpeterson579's avatar SnapeyEloquent
5

how to use flatten() in eloquent query ?

how to use flatten() in eloquent query ? and convert flattened data to 'toArray()'.

random@94's avatar
random@94's avatar SnapeyLaravel
10

having condition in a Laravel eloquent query.

Hi I have an eloquent query and the table I am querying contains a column named active. So active could be 1 or 0. Now I am querying all the records from the table, that means I have all the records which may have active = 1 or active = 0. Now what I want to do is that I want to show or get the active = 1 records first then the those records which have active = 0. //now Imagine

eddy1992's avatar
eddy1992's avatar tykusEloquent
5

Get paginated posts and also get status count in one query

Hello I need a help with fetching paginated collection of posts with count of status in one query. My post index query is public function index(PostFilter $filter) { return Post::with(['author', 'category', 'tags']) ->withTrashed() ->filter($filter) ->paginate(request()->perPage ? request()->perPage : 15); } Post Status count query i

gurinder's avatar
gurinder's avatar gurinderEloquent
0

hasOne query existence.

how do I query hasOne existence? What I want to achieve is, if the user has not register their company's contact, Add Company Contact will appear. this is my attempt: controller $companyContact = Company::find($id)->companycontact()->get(); view @if($companyContact->isEmpty()) <p><a href="#">Add Company Contact</a><p> @endif

msyaukat's avatar
msyaukat's avatar richardLaravel
1

Generate query URL

Hey everybody, I'm searching for a method in PHP that generates a full query URL by passing a base URL (which could already include a query string) plus additional parameters. I know that I could check if the URL contains a "?" and if so append with "&", otherwise append with "?". The question here is, if PHP has such a method on board? I'll gi

bart's avatar
bart's avatar CorsariGeneral
2

Laravel 5.5 Eloquent Resources + GET Query Params

Hi guys, I wrote an article on a quick and easy way to load GET query param includes into your Eloquent models so that you can use the new Laravel 5.5 Eloquent Resources feature very easily without making your code look messy! Let me know what you think :)! https://medium.com/@matthew.erskine/laravel-5-5-eloquent-resources-get-query-param-filters-9101edad2d95 Cheers!

matthew.erskine's avatar
matthew.erskine's avatar tisuchiEloquent
1

How to change Laravel query?

$stock_qry=''; if ($this->configVal('outOfStock') == 2) { $stock_qry =" AND (( tyv.option_stock > 0 ) OR (p.enquiry=1 OR p.pb_sku!=''))"; } SELECT option_name , GROUP_CONCAT(DISTINCT option_value ORDER BY CASE WHEN option_name IN('size') THEN option_value + 0 ELSE LENGTH(option_value) END) option_values FROM (SELECT tyv.id, parent_option_name option_name, pare

png_n's avatar
png_n's avatar bobbybouwm...Laravel
1