StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL multiple values in fields, EAV, etc
text
Body
copied!<p>Sorry the title couldn't be a more descriptive I still don't know the name of what I'm dealing with.</p> <p>I'm developing a search system for a realty site and it was working well until I realized I had forgotten to take for account that some of my fields (which are used as filters in the search page) could be multiple values. What I mean is that I had only one field for Sale and Rent and one only one field as well for Residential and Commercial (other fields too) -- the problem is that a property could be for sale or rent or could be residential, commercial and industrial.</p> <p>What I thought I'd do is move those to their own table. The thing is, I have yet another table that lists the possible values for each field, which is used to display value names, populate forms, used as constraints, etc.</p> <p>So now I'm stuck with a myriad of tables.</p> <p>A table for properties, then tables for values, then tables that join these values to the properties.</p> <p>A plain search query with no filters (more filters, more joins) I'm at 7 inner joins.</p> <p>I'm having an extremely hard time getting joins to work both for constraining results and returning values in the SELECT portion of the query.</p> <p>I'd appreciate any suggestions as this problem has left me mentally fatigued for the last 2 days.</p> <p><strong>EDIT:</strong></p> <p>I have the following tables at the moment:</p> <p>properties<br/> properties_images<br/> properties_listings<br/> properties_options<br/> properties_purposes<br/> res_geo_address<br/> res_geo_cities<br/> res_geo_neighborhoods<br/> res_geo_states<br/> res_property_options<br/> res_property_type_age<br/> res_property_type_listing<br/> res_property_type_property<br/> res_property_type_purpose<br/> res_property_type_purpose_property<br/></p> <p>The properties table is the main property table, has a handful of colums that describe the property.</p> <p>The properties_ tables are used to bridge the properties table and the res_property_ tables (usings ids - one for the property and one for the value) (exception is the images, which just contains image records)</p> <p>The res_ tables list id and value names for the most part. (exception are the res_geo tables)</p> <p>As of right now, my queries don't work. To be honest I've reached such intricacy that is hard for me to explain my current setup.</p> <p>I'd be nice if there was a simple way to query tables with fields that have arrays and easily retrieve those.</p> <p><strong>EDIT 2:</strong> </p> <p><img src="https://i.stack.imgur.com/kM4EM.gif" alt="alt text"></p> <p>The image above shows one of the joins. How would I be able to query for a property, always retrieve a concat of all the listing name associated with it but optionally limit the properties by a certain group of listing ids?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload