small medium large xlarge

Generic-user-small
11 Jul 2016, 23:37
Reginald Davis (8 posts)

I’ve created my Customer Detail Materialized View migration and even when I go into my console and pull up tables I see the customer_details table. However, I’ve been trying to query the inserted customer with the customer id but when I look it up through the customer_details table it will not show up. Yet, the customer shows up in the customer tables. What am I not doing for my customer table and customer_details table to not join properly?

Here’s my github page…

https://github.com/madblkman/Shine

Dmfcb_pragsmall
11 Jul 2016, 23:49
David Copeland (437 posts)

Are you refreshing the view? With a materialized view, you have to refresh it whenever something changes. That chapter should go over the syntax and the details.

Generic-user-small
12 Jul 2016, 03:03
Reginald Davis (8 posts)

Yes, I’ve tried re-entering the customer and refreshing the table. But once I do both, the query results show 0 rows.

Dmfcb_pragsmall
12 Jul 2016, 18:05
David Copeland (437 posts)

What happens when you run the SQL directly? e.g. rake dbconsole and then select * from customer_info where id = XXX? What happens when you run the query backing the materialized view? I wonder if there’s a syntax error there or an errant join?

Generic-user-small
16 Jul 2016, 06:34
Reginald Davis (8 posts)

When I query from the customer_details table (materialized view) I get an empty table with (0 rows) at the bottom.

Dmfcb_pragsmall
17 Jul 2016, 17:17
David Copeland (437 posts)

Can you try running the query that’s backing your materialized view? If you made some sort of typo in it, the view could be empty even if there is data in your database.

You can use \d+ «view name» in psql to show the query the materialized view is based on. It should show up after the words “With definition:”. copy/paste that and see if it returns values.

Generic-user-small
04 Aug 2016, 06:26
Reginald Davis (8 posts)

Sorry for the delay…but I checked my materialized view and it isn’t based on anything it looks like.

Materialized view "public.customer_details"
       Column        |            Type             | Modifiers
---------------------+-----------------------------+-----------
 customer_id         | integer                     |
 first_name          | character varying           |
 last_name           | character varying           |
 email               | character varying           |
 username            | character varying           |
 joined_at           | timestamp without time zone |
 billing_address_id  | integer                     |
 billing_street      | character varying           |
 billing_city        | character varying           |
 billing_state       | character varying           |
 billing_zipcode     | character varying           |
 shipping_address_id | integer                     |
 shipping_street     | character varying           |
 shipping_city       | character varying           |
 shipping_state      | character varying           |
 shipping_zipcode    | character varying           |
Indexes:
    "customer_details_customer_id" UNIQUE, btree (customer_id)

…this is what returns when I take a look at the table.

Dmfcb_pragsmall
04 Aug 2016, 11:35
David Copeland (437 posts)

That doesn’t look like the output of \d+ «view name» Can you try using \d+ and paste what it shows? That should show the actual definition of the table.

Generic-user-small
07 Aug 2016, 23:00
Reginald Davis (8 posts)

Ok, here’s what I got for the definition…

View definition:
 SELECT customers.id AS customer_id,
    customers.first_name,
    customers.last_name,
    customers.email,
    customers.username,
    customers.created_at AS joined_at,
    billing_address.id AS billing_address_id,
    billing_address.street AS billing_street,
    billing_address.city AS billing_city,
    billing_state.code AS billing_state,
    billing_address.zipcode AS billing_zipcode,
    shipping_address.id AS shipping_address_id,
    shipping_address.street AS shipping_street,
    shipping_address.city AS shipping_city,
    shipping_state.code AS shipping_state,
    shipping_address.zipcode AS shipping_zipcode
   FROM customers
     JOIN customers_billing_addresses ON customers.id = customers_billing_addresses.customer_id
     JOIN addresses billing_address ON billing_address.id = customers_billing_addresses.address_id
     JOIN states billing_state ON billing_address.state_id = billing_state.id
     JOIN customers_shipping_addresses ON customers.id = customers_shipping_addresses.customer_id AND customers_shipping_addresses."primary" = true
     JOIN addresses shipping_address ON shipping_address.id = customers_shipping_addresses.address_id
     JOIN states shipping_state ON shipping_address.state_id = shipping_state.id;
Dmfcb_pragsmall
08 Aug 2016, 01:20
David Copeland (437 posts)

Cool, that looks good. Nowm what happens if you copy and paste that into your SQL prompt? You can add a where customer_id = «some id»; to not get every row back. Just use an id that’s in your customers table (e.g. select id from customers limit 1;).

If you don’t get anything back, check to see if each table has rows in it for the customer you are looking up. In particular, check that the states table has stuff in it. The way those joins work, if any table is missing data, you get nothing back.

Generic-user-small
12 Feb 2017, 16:45
Hemal Varambhia (1 post)

Hi, Dave. I’m following the book at the moment and when I run EXPLAIN ANALYZE on the queries on page 155, I get Seq scan. I checked your structure.sql script and I noticed that you had

-- Name: index_customers_billing_addresses_on_address_id; Type: INDEX; Schema: public; Owner: - --

CREATE INDEX index_customers_billing_addresses_on_address_id ON customers_billing_addresses USING btree (address_id);

-- -- Name: index_customers_billing_addresses_on_customer_id; Type: INDEX; Schema: public; Owner: - --

CREATE INDEX index_customers_billing_addresses_on_customer_id ON customers_billing_addresses USING btree (customer_id);

My issue is that there doesn’t seem to be any migration for these. Did you add the above commands directly?

Generic-user-small
21 Mar 2017, 02:19
LIN SHIH HUNG (2 posts)

Hi Dave, I encounter the question as same as Hemal mentioned. Do you have any suggestion ?

You must be logged in to comment