I am new to ExpressionEngine2 and already working with it, but find the database structure very confusing coming from a pure OOP environment. What is an easy, fast way to actually find references or foreign keys / data that you are looking for?
4
-
Not sure what OOP has to do with how a MySQL database is structured ... can you clarify what exactly are you looking for? The database is abstracted, sure, but not that heavily.– Derek HogueCommented Jun 3, 2014 at 14:18
-
Not looking for anything specific, just trying to find an easy, fast way navigating between the different tables.– heinkasnerCommented Jun 3, 2014 at 14:20
-
1Sorry, still not getting you - navigating between the tables? This seems a little broad, can you narrow it down to a specific use case that someone can answer?– Derek HogueCommented Jun 3, 2014 at 14:24
-
2To add to Derek's questionning - why are you trying to pull data out directly, thus need to understand the underlying data? EE gives you the ability to pull all the information in via template tags, so you don't need to worry about relationships or the way table data is threaded. You only need to start delving into the data linking and structure if you're building add-ons or complex functionality that requires key data. If that's the case, it may be better to give a requirement example rather than a loose statement, so your question can be better answered.– Peter LewisCommented Jun 3, 2014 at 15:12
Add a comment
|
1 Answer
My answer assumes you are for example trying to pull channel data manually. I'm not entirely sure of the OOP / Foreign key part of the question.
First you need to understand the exp_channel_titles and exp_channel_data tables. I will only go into the information that's relevant for basic database interaction.
exp_channel_titles contains the basic entry information without the custom fields. Think of the title, url_title, sticky, entry_date and so on.
exp_channel_data contains the custom field data of entries. The columns are named after the field id's that can be retrieved from the exp_channel_fields table.
So say you wan't to query a certain entry and you only have the url_title available you might want to do something like this:
SELECT
`exp_channel_titles`.`title`,
`exp_channel_data`.`field_id_1`
FROM
`exp_channel_titles`
LEFT JOIN
`exp_channel_data` ON `exp_channel_data`.`entry_id` = `exp_channel_titles`.`entry_id`
WHERE
`exp_channel_titles`.`url_title` = 'lorem-ipsum';
Or this:
$result = ee()->db
->select('exp_channel_titles.title, exp_channel_data.field_id_1')
->join('exp_channel_data', 'exp_channel_data.entry_id = exp_channel_titles.entry_id')
->get('exp_channel_titles')
->result_array();
Of course this is a very simplistic scenario. There are fields that have tables of their own that need to be taken into the queries. In some cases this can get quite complicated.
If you want to find relationships between entries you need the exp_relationships table. It's a many to many setup where you have a parent and child id for the entries.
I hope this is what you are after.