Skip to content

Instantly share code, notes, and snippets.

@SOHELAHMED7

SOHELAHMED7/README.md

Last active Jun 25, 2020
Embed
What would you like to do?

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

--
-- 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
--
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.