Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Automatically filtering with model inheritance based upon the value of a field

Lets say that I have a table called data and a model called Data. Then I have Files that inherits from Data. Data is an abstract type and I want to store the actual type of the data in a single field.

I'm wondering how to by able to say Files::getFirstByVal($val) and have it supply the automatic SQL condition type = "file" when returning the rows. It would be fine in my case if I had to override some methods in the base class or in each class that inherits from Data.


When you use \Phalcon\Mvc\Model::findFirst(), that's actually a shortcut to \Phalcon\Mvc\Model::find(). findFirst() just adds some extra conditions, then calls find(). You can do the same thing with your custom getFirstByVal():

public function getFirstByVal($val){
    $parameters = ['conditions'=>'key = :val: and type = "file','bind'=>['key'=>$val],'limit'=>1,'order'=>'key'];
    return parent::find($parameters);

... or something to that effect.

@quasipickle Is there a more generic way to do that with everything that is not being called by the primary key?

Also, is there a nice way to reject data on a DB Select lets say that I tried to load a Hats record into Files if they were both inheriting from the base table/Model?

"key" in my example was just meant to represent whatever column name you're searching by - not the actual primary key.

If you add your getFirstByVal() method to your base model class (that is then extended by your other models such as Hats or Files), then when you call Hats::getFirstByVal(), it will automatically load from the hats table, not files. So in short - there's no way to load a Hats record into a Files model using this technique. If you got right down to PHQL, then the sky's the limit for what you can mess up ;)

In my case Hats and Files are stored in the same table. I have basically an elaborated Vokuro setup and all user data is stored in a single table.

I think that I could solve this in the base class by looking up the current object type to fill in that filter.

As far as PHQL is it possible to do a FULLTEXT search on a field? I have a rather nice fulltext solution that allows me to know the object inheritance chain at the database level and its more than performant enough. The new MariaDB 10 stuff will make it even tighter. Anyways last I checked for research probably over a year ago it wasn't possible to do FULLTEXT with PHQL, or maybe it was a niggle of some kind.

An alternative to looking up the current object type is to just have a class constant that you referenc - save on a function call every time.

PHQL supports everything PDO does, and I believe PDO supports FULLTEXT searching.

Ok @quasipickle what do you say to this one:

Apparently it can't be done?

Also this stack overflow.

It would really save me a lot of time to know this. I'd hate to have to ditch PHQL just because of that. I think that using FULLTEXT to emulate an inheritance chain works out very well and its performant. Also MariaDB 10 allows something like integer constants to be used (I forget though the exact thing since it was a while ago but it looked real awesome).

Well I guess I was wrong then.

Further down in that post you linked to, it was said that a FULLTEXT search would slow down your application, and that's correct. FULLTEXT searches are basically only useful when you're wanting to search a lot of text. Could you explain more how you want to use FULLTEXT searching?

edited Feb '15

Lets say that I had the following type of taxonomy:

  • data -> file
  • data -> file -> doc
  • data -> file -> video
  • data -> tag

So I use the fulltext field to store a doc like this cfile cdoc. Then I match against whatever level that I want to search at like I can say give me all of the files or give me all of the docs. Then I store another field in the row to specify the most specific superset type that it is in this case doc.

It ends up working very well. Its performant for me. MariaDB 10 will speed this up as well by storing less data. I don't find that it is at all worth it to normalize in my case and create another table and then another merged table. I do use a link table for other stuff though and that works good.

I definetely want to continue using this.

So some Mysql stuff changed with 5.6. Is it possible now with PHQL? Is Phalcon getting in the way of something because of lowest common denominator?

edited Feb '15

Also I'll add that my product ends up having a lot of disjoint data sets that I store in separate databases. One particularly large "site" might end up being just 20-30k user created data rows. I do some complicated DB unions in PHP code. I know absolutely that one disjoint set is not needing to scale up to millions and some stuff is just impossible to do with SQL. I've figured out ways to dramatically limit down the dataset to a small fraction of its total size on the first SQL query and so the key list ends up being just perhaps a few hundred large at most. It works! and I understand the limitations.

Well you sound like you've done your research, so I won't bark up the "normalization" tree.

edited Feb '15

Ok though but can I use MATCH AGAINST with PHQL? Mr. Phalcon said no a while ago but other people were saying no about it specifically for PDO MySQL on stackoverflow until someone corrected them that it had changed for MySQL 5.6. So something changed but I'm not sure if that trickled into Phalcon land.

Hmm - I'm not sure. I think PHQL is "built" so to speak, on top of PDO. So while you may have an updated MySQL installation, and an updated PDO extension, Phalcon has to be built and coded to take advantage of the new features. Again, just my guessing - maybe @Phalcon would have more insight.

Sure would be swell. I want to use PHQL.

@quasipickle there is a Mysql dialect file in the Incubator that adds Fulltext support. It looks fairly easier to extend it so I'll likely end up adding support for a new MariaDB multicolumn feature.

@quasipickle It looks like I might end up going with a more correct third normal form since I've been able to break all legacy structures. I've broken the inheritance chain data into a separate table but I'm still storing the most specific type in the actual data row to prevent having to look that up every time. It looks like the most involved query will be slower but perhaps not much worse than 0.01s slower in some cases and there isn't a bad worst case that will cause things to get out of control. I might end up creating a bit of a precalculated table on inserts if it seems like its necessary. I'm probably gaining so much with Phalcon that I'll still have a lot of room there.

Anyways, FULLTEXT in binary mode is a fantastic way to add object inheritance search features to a table when creating additional tables isn't an option. The performance isn't so bad for it really and I think that when people speak about FULLTEXT being slow they are referring to the normal fulltext stuff (without binary option) that is better done with Lucene.