Problem implementing an object database

[simonrs]

Hi all,

Would really appreciate some help on a problem I'm having.

 

Every piece of content on my site is an extension of a php class called "Content". One of the variables/properties of this class is a "fields" array, which stores as keys => values all fields for that piece of content. For instance, this array might look like this for a blog post:

 

array('author' => 'simonrs', 'title' => 'my blog title', 'content' => 'blog content bla bla bla');

 

My database schema for storing all these content objects in two tables is as follows:

 

content: id, class

content_fields: content_id, field, value

 

So in the above example, it will be stored as 1 row in content:

1,BlogEntry

 

And a few in content_fields:

1,author,simonrs

1,title,my blog title

1,content,blog content bla bla bla

 

Onto my problem! I want to, in one database select query, search over more than one field for a particular content class - for instance, search over an author's name and a blog entry's title at the same time.

 

The problem is, that I can't think of how to design such a query as it must involve two rows from the same table, both extracted as part of the same "result row".

 

Is this possible or is a schema redesign advisable?

 

Thanks greatly in advance for any help any one can offer me.

Simon

[corbin]

You're wanting to search serialized arrays in rows?  Ewwwwwwwwww.

[simonrs]

Ok... could you suggest a better schema that doesn't involve dynamic adding of tables or columns?

[trq]

Your schema seems fine to me, I think corbin misread your post.

 

Given your description, your query will indeed return two rows as its result, I don't however see the proplem with that.

[simonrs]

But if I wanted to search over two fields, how would that work?

 

Let me give a simple query (may not be syntactically perfect but you'll get the idea) that will work, for searching on the title of a blog:

 

SELECT `content`.`id`, `content`.`value` FROM `content`, `content_fields` WHERE `content`.`class` = 'blog' AND `content`.`id` = `content_fields`.`content_id` AND `content_fields`.`field` = 'title' AND `content_fields`.`value` LIKE '%search text%'

 

That'll work fine, returning to php a data structure of all matching content id's and titles.

 

What if I wanted to extend that query, though, so as well as the titles, it also returned the body and author of each post? They don't have a different column name, so they can't be specified in the list of columns to select.

 

And what if I wanted to extend it further to search over more than just the title, say it had to match a certain title string, and be by a certain author?

 

Unfortunately I want (or at least, want to be able to have) the ability to do both those things, so I think I'm looking at a schema change. Any ideas? Or am I going to be forced to add a table for each content type, which I'd prefer not to do as I'd have to change the database manually or by code (messy) everytime I add a new content type or change the schema of one.

[corbin]

Yeah I think I entirely misunderstood your first post.  Sorry hehe.

 

 

 

Anyway, maybe an IN() clause could work?

 

 

WHERE field IN('author', 'title', 'content')

 

for example.