I want to export some results in Excel from the Model method, so I added this, public static function getAllData() { $records = DB::table('orders as t1') ->leftjoin('payments as t2', 't1.ord_id', '=', 't2.pay_ord_id') ->select(DB::raw("case when pay_type_id=190 then t1.ord_total - t2.pay_amount as…
I have values array as : const params = [ ['2022-12-10', 'aaaaa', '2022-12-01', 'xhxha', '2022-12-10'], ['2022-12-11', 'ababa', '2022-12-01', 'xhxha', '2022-12-11'], ['2022-12-12', 'acaca', '2022-12-01', 'xhxha', '2022-12-12'], ['2022-12-13', 'adada', '2022-12-01', 'xhxha', '2022-12-13'], ]; const data = await db.query(`select id, title, DATE_FORMAT(end_date,"%Y-%m-%d") as…
In SQL, how can I select the minimum value of a column a (integer) where the column b (integer) is the maximum value for that column, using only one select ? SELECT MIN(a) OVER (PARTITION BY MAX(b)) as res #seems…
Sample on sqlfiddle - Where I run a query like below - select CONCAT(id, rev) as new_id from DOCS where new_id>1 i am getting below error - Unknown column 'new_id' in 'where clause' Why does not sql allow to use…
I have a table named customers, that has many columns: 'id', 'name', 'age', 'weight', 'hight', 'blood', 'sex', 'last name', 'datetime'. I want to add a checking logic before inserting, which is if the datetime already exists, then update/overwrite the whole…
I have 2 tables: First one is bom Article AB CD EF GH CREATE TABLE bom ( Article VARCHAR(250) ); INSERT INTO bom (Article) VALUES ('AB'), ('CD'), ('EF'), ('GH'); Second one is purchases Article OrderDate Price AB '2020-01-10' 12 AB…
I'm trying to find out, why a simple aggregate query performs really slow on Postgres 15 from my point of view. I have a growing data table with 10 mio records of sensor data CREATE TABLE public.ts_number ( id int4…
I need some help here, my request is : List the bookID and number of copy of this in Library (Number of copy in library = number of copy – number of copy student borrow but not return yet) and…
As far as I know, If I want to create a new database, normally fistly I have to create and switch on a server (in this case a local server in my computer), Secondly I connect my RDBMS (MySql) to…
I use this table in a PostgreSQL database: create table if not exists "Service" ( _id uuid not null primary key, service text not null, "count" integer not null, "date" timestamp with time zone, team uuid, organisation uuid, "createdAt" timestamp…