Category : sql

I have 3 relational tables Cart, CartProducts and Products and in the query I have the low JSON result, but I want to add the product name and description information in cart_products. Relationships: Cart with CartProduct public function cartProducts() { return $this->hasMany(CartProduct::class); } CartProduct with Product: public function products() { return $this->belongsTo(Product::class); } And the ..

Read more

<?php namespace AppHttpControllers; use IlluminateHttpRequest; use DB; use AppModelsUser; use AppHttpRequests; use IlluminateSupportFacadesAuth; use AppHttpControllersController; use Redirect; class Insertlikes extends Controller { public function insertlikesform() { return view(‘dashboard’); } public function insertlike(Request $request) { $book_id = $request->input(‘book_id’); $user_id = Auth::user()->id; $user_name = Auth::user()->name; if(DB::select("select count(*) from likesrate where user_id=’$user_id’ and book_id=’$book_id’")==0) { DB::insert(‘insert into likesrate ..

Read more

I want to insert " [email protected]#$%^&*()?<>:| " this string into my table but when I hit save it inserts only this much in table [email protected]#$%^, rest of the characters doesn’t store in the table. Here is my laravel controller code public function store(Request $request) { $x = $request->get(‘task_name’); DB::insert(‘insert into tasks (task_name) values (?)’, [$x]); ..

Read more

I’m trying to count the daily average number of game plays, per user, but failing to see how to avg, count and group altogether. Activity::where(‘action’,’play’) ->groupBy(‘user’) // or per day? ->get(array( DB::raw(‘COUNT(*) as "total"’) )) ->avg("total"); Essentially I’m trying to get a number that says in average there’s X plays per user per day Source: ..

Read more

i have the next query with two subequery how can convert it to eloquent, i have some troubles with eloquent conversion :S. Thanks select * from (select * from ( select * from "public"."tabla_procesados" where "state" LIKE ‘%Failed%’ and id_execution = (select max(id_execution) from public.tabla_procesados) order by public.tabla_procesados.ts_start_processing desc ) a limit 1) b where ..

Read more

I want to use the different queries inside one method and I want to pass a part of query inside that. My method looks like this: static function methodName($partOfQuery) { ModelName::where(‘…’)->$partOfQuery->…; } And I want to do something like: $partOfQuery = where(‘columnName’, ‘>=’, 5)->whereRaw(‘Other Condition’); self::methodName($partOfQuery); But I was faced with this error: Call to ..

Read more

public function dataTable($query) { return datatables() ->eloquent($query) ->addIndexColumn() ->addColumn(‘leave_type_id’, function ($data) { // $data is data od current row in table return $data->leaveType->name ?? ”; }) ->addColumn(‘status’, function ($data) { // $data is data od current row in table if ($data->status == 1) { return ‘<button class="m-2 btn btn-success btn-sm" type="button">Granted</button>’; // or simply return ..

Read more

This is in Laravel and its js I am trying to retrieve data from the column "message_template" (data type: text). in my code, I am using the DataTable plugin for js. "row" retrieves the data from "#main-message-list" let data = $(‘#main-message-list’).DataTable().row( $(this).parents(‘tr’) ).data(); $(‘#message_template’).val(data.message_template); The issue with the code above is the HTML Entities in ..

Read more

I am using PostgreSQL 12. I have ‘users’ table where ’email’ column is nullable means optional column, however it has unique constraint as expected. This ’email’ column is ‘citext’ type column. The problem is even I send ’email’ value as NULL, the database saves it as empty string instead NULL in ’email’ column during insert. ..

Read more

I have a complex query:- $sql = "SELECT dateQ.selected_date, apiQ.no_of_error AS error FROM ( SELECT * from (SELECT adddate(‘1970-01-01’,t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT ..

Read more

I have a model Product with a hasOne relation getCompany. public function getCompany(){ return $this->hasOne(Company::class, ‘id’, ‘company_id’); } I need to retrieve the Product collection sorted by getCompany.rate My approach $products = Product::with(‘getCompanyName’, ‘getCompany’) ->whereHas(‘getCompany’, function ($query) { return $query->where(‘status_shop_id’, ‘=’, 2); }) ->whereHas(‘getCompany’, function ($query) { return $query->where(‘status’, ‘=’, 1); }) ->where(‘name’, ‘Like’, "%$text%") ..

Read more

need some help with a query on Laravel(PHP) what I have : Two tables. Expedients & ExpedientRevs with a hasMany relationship beetween them. Expedient Model: public function ExpedientRevs() { return $this->hasMany(‘AppExpedientRev’); } What I need: A list of the expedients (Distinct Expedient) order DESC by the last Expedients Revisions (last id of ExpedientRev) I mean, ..

Read more