Joining ~ 40 normalized tables for 200 columns to display detailed user profile. Can this be optimized?

A profile can have many profile types. That profile can have many fields, which are all tied to lookup tables. Some fields can have many selections.

profile -> profile_date -> one relationship status, one zodiac, many languages, many countries traveled, many cuisines, many activities, ...  profile -> profile_network -> one company, one school, ...  profile -> ... 

A lookup table for the fields looks like this:

id  type 1   ♈ ARIES 2   ♉ TAURUS 3   ♊ GEMINI 4   ♋ CANCER 5   ♌ LEO 6   ♍ VIRGO 7   ♎ LIBRA 8   ♏ SCORPIO 9   ♐ SAGITTARIUS 10  ♑ CAPRICORN 11  ♒ AQUARIUS 12  ♓ PISCES 

If the field is a single selection, we join the lookup table and we’re done.

-> profile_date (zodiac_type_id) -> zodiac_type.id 

If the field is a multiple selection, there’s a join table in between for the many-to-many.

-> profile_date -> (profile_date_id) profile_date_languages (language_type_id) -> language_type.id 

In addition, I am joining other things like this to get all of their photos:

profile -> personas -> photos 

In addition, the query also filters for things like persons who like each other, etc.

The query seems to start performing poorly with just a low number of profiles, like less than 10. If a single person has an obnoxious profile where they check all the multiple selections e.g. speak every language, been to every country, it grinds to a halt for everyone if their profile ends up being fetched.

That’s the idea. You can see more of the fields here:

Sample query: https://pastebin.com/wug86Wrr

Sample query plan: http://www.sharecsv.com/s/a6333a7f610e049dd81ebcfc19a4c02f/temp_file_limit_exceeded.csv

Users will always be filtering by at least profile.birth_date (age), profile.radius (geolocation).

PostgreSQL 9.6

Some ideas I had were:

  1. Optimize this query (I am not sure what can be done, am mostly wondering if I’m just doing something really stupid/unreasonable)

  2. Right now I am joining many tables per card, so the initial load is slower, but the data is all there if the user decides to explore and things look instant to them. I could instead fetch the minimum preview for the cards (join few tables per card), then only join the full profile (1 card filtered by id, many joins) if the user clicks on the preview to see the detail view (but I feel this is not the best user experience because the user has to wait for the load every time they click a profile)

  3. Use ElasticSearch (I feel like I wouldn’t need this until many more profiles?)

” a field on Manage display

I’m using D7.

If you set a field <Hidden> on ‘Manage display’, and, unless you display it by views or something, you cannot see it anywhere on the site, right?

They say…

Format
The format column contains the options to show the field value in different formats. Hidden is the global value which is present in all the format option list, if you choose hidden then it will hide the whole field from showing on the content page.

https://www.drupal.org/docs/7/nodes-content-types-and-fields/specify-how-fields-are-displayed

I understand it hides a field from View mode. But, you can also hide it if you don’t try to do by using those modules, which are designed to output fields, like Views, right? Or, is there still any possibility that users can see the hidden field even if those modules are not used?

I’m sorry if this is a very basic question. But, I wanted to make sure what ‘hidden’ field means.

Thank you.

How to display data from a parent site list on a subsite home page

This is best explained using an image: Inheriting data from parent site list

I have a parent site with a list of products, each of which has its own Project sub-site. I want the home page of each sub-site to display data about its record from the list in the parent site. The text in the subsite can be plain text – it doesn’t need to be edited or affect the original list in the parent site. The info should change if the record in the parent list is updated though.

Is there a way to do this without InfoPath? Is there a web part that will work?

Thanks!

Magento 2: New Customers display issue in Admin Customer Grid

In Magento 2.1.8, Customer Grid is populating from table customer_grid_flat rather than customer_entity. This is for fastest retrieval of data. But the problem is customer_grid_flat never gets populate until I manually reindex the grid using SSH.

For a quick hack I have changed the code in blow file

Line 37: vendor/magento/module-customer/Model/ResourceModel/Grid

Old Code

$  mainTable = 'customer_grid_flat', 

New Code

$  mainTable = 'customer_entity', //'customer_grid_flat', 

Now some columns are not showing data like Name, Phone, Country etc. because of missing joins with relevant tables. Can anyone please suggest any better solution.

Material Design – How to display an error on a larger screen

A bit similar to this question Material Design – How to display a log in error

Though it doesn’t address how to do this on a larger screen (i.e. desktop browser) since snackbar at the bottom as an error message is a bit weird on a larger screen, please see image below.

enter image description here

Official material design guidelines doesn’t include any web-related guidelines for this case.

I’m currently using angular 5 and angular-material which is designed to work both on desktop and mobile.

Taxonomy terms with associated nodes: display page by page navigation in node page?

Hi i have a content type called Product and have fields(title,description,code,image) and also have vocabulary categories like below formate

parent term 1   -child term 1     --sub child term 1.1     --sub child term 1.2     --sub child term 1.3     --sub child term 1.4     --sub child term 1.5 parent term 2   --child term 2     --sub child term 2.1     --sub child term 2.2     --sub child term 2.3     --sub child term 2.4     --sub child term 2.5 . . parent term 5   --child term 5     --sub child term 5.1     --sub child term 5.2     --sub child term 5.3     --sub child term 5.4     --sub child term 5.5 

so i want to display my product node page first parent terms list when i click parent term 1 then it’s display all related child terms in next page after that when click child term 1 display all related sub child terms list in another page after that when i click sub child term 1.1 need to display related products list with above fields.Please find below flow of screen shots.

Taxonomy Parent Terms list Step 1 enter image description here

Step 2 when i clk Parent term 1 need to display all related child terms in another page like below enter image description here

step 3 when i clk child term 1 need to display all related sub child terms in another page like below enter image description here

step 4 when i clk sub child term 1.1 need to display all related node i.e product items list with above fields info. enter image description here Please advice Thanks.