Postgresql queries mentioned in https://sohelahmed.site/blog/sorting-calculated-columns-in-yii-2

README.md

Instructions:

Created view query is strangely different in the query.sql file that is generaed by pg_dump command. Original and simple one is shown in the blog

Download the query.sql file locally.

Find & replace pgsql username pgsql_user_name with your actual one

Then import by command - $ psql -h localhost -d db -U pgsql_user_name -f query.sql (at the CLI prompt not the postgre prompt)

Blog link: https://sohelahmed.site/blog/sorting-calculated-columns-in-yii-2

query.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.13 (Ubuntu 10.13-1.pgdg18.04+1)
-- Dumped by pg_dump version 12.3 (Ubuntu 12.3-1.pgdg18.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: orders; Type: SCHEMA; Schema: -; Owner: pgsql_user_name
--

CREATE SCHEMA orders;


ALTER SCHEMA orders OWNER TO pgsql_user_name;

SET default_tablespace = '';

--
-- Name: order_items; Type: TABLE; Schema: public; Owner: pgsql_user_name
--

CREATE TABLE public.order_items (
    id integer NOT NULL,
    order_id integer NOT NULL,
    product_id integer NOT NULL,
    quantity integer NOT NULL
);


ALTER TABLE public.order_items OWNER TO pgsql_user_name;

--
-- Name: orders; Type: TABLE; Schema: public; Owner: pgsql_user_name
--

CREATE TABLE public.orders (
    id integer NOT NULL,
    user_id integer NOT NULL,
    created_at timestamp without time zone
);


ALTER TABLE public.orders OWNER TO pgsql_user_name;

--
-- Name: products; Type: TABLE; Schema: public; Owner: pgsql_user_name
--

CREATE TABLE public.products (
    id integer NOT NULL,
    name character varying NOT NULL,
    price integer NOT NULL
);


ALTER TABLE public.products OWNER TO pgsql_user_name;

--
-- Name: order_with_total_amount; Type: VIEW; Schema: public; Owner: pgsql_user_name
--

CREATE VIEW public.order_with_total_amount AS
 SELECT ord.id,
    ord.user_id,
    ( SELECT sum((oi_in.quantity * prd_in.price)) AS sum
           FROM ((public.orders ord_in
             LEFT JOIN public.order_items oi_in ON ((ord_in.id = oi_in.order_id)))
             LEFT JOIN public.products prd_in ON ((oi_in.product_id = prd_in.id)))
          WHERE (ord_in.id = ord.id)) AS total_amount
   FROM public.orders ord
  ORDER BY ( SELECT sum((oi_in.quantity * prd_in.price)) AS sum
           FROM ((public.orders ord_in
             LEFT JOIN public.order_items oi_in ON ((ord_in.id = oi_in.order_id)))
             LEFT JOIN public.products prd_in ON ((oi_in.product_id = prd_in.id)))
          WHERE (ord_in.id = ord.id));


ALTER TABLE public.order_with_total_amount OWNER TO pgsql_user_name;

--
-- Data for Name: order_items; Type: TABLE DATA; Schema: public; Owner: pgsql_user_name
--

COPY public.order_items (id, order_id, product_id, quantity) FROM stdin;
1   1   1   1
2   2   1   1
3   2   2   1
4   2   3   2
5   3   3   1
6   3   1   1
\.


--
-- Data for Name: orders; Type: TABLE DATA; Schema: public; Owner: pgsql_user_name
--

COPY public.orders (id, user_id, created_at) FROM stdin;
1   1   2009-12-12 00:00:00
2   2   2010-12-12 00:00:00
3   3   2011-12-12 00:00:00
\.


--
-- Data for Name: products; Type: TABLE DATA; Schema: public; Owner: pgsql_user_name
--

COPY public.products (id, name, price) FROM stdin;
1   mobile  10000
2   keyboard    600
3   mouse   200
\.


--
-- Name: order_items order_items_pkey; Type: CONSTRAINT; Schema: public; Owner: pgsql_user_name
--

ALTER TABLE ONLY public.order_items
    ADD CONSTRAINT order_items_pkey PRIMARY KEY (id);


--
-- Name: orders orders_pkey; Type: CONSTRAINT; Schema: public; Owner: pgsql_user_name
--

ALTER TABLE ONLY public.orders
    ADD CONSTRAINT orders_pkey PRIMARY KEY (id);


--
-- Name: products products_pkey; Type: CONSTRAINT; Schema: public; Owner: pgsql_user_name
--

ALTER TABLE ONLY public.products
    ADD CONSTRAINT products_pkey PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--