using external tables with elgg?

Hi,

I'm wondering if anyone else out there uses external tables with elgg? Although elgg provides functions to query the elgg tables based on subtype and all that, I think it would be a good idea to store certain things in their own non-elgg tables. This would provide much more versatility when it comes to querying, joining, and ordering.

Does anyone else do this? And if so, do you have any tips on what should remain in elgg tables, and what should be separated into non-elgg tables?

Thanks!

John

  • We regularly use use non-elgg tables to store specialised data. There's no real practical need to "comply" with elgg's datastore specs - esp if doing so improves general performance.

  • I think that it's a bad idea to add additional tables to Elgg itself. (Integrating other applications is a different issue.)

    I have no idea why Dhrup has done this, but in my experience most people suggesting the idea of additional tables are new to Elgg and do not understand the API very well. In that case, adding additional tables (especially for visible content) may make an inconsistent mess of your install as Elgg does a huge number of things "under the hood" for you and additional tables would break that.

  • The latest version of the API makes it very easy to add additional where and and join clauses to what is generated by elgg_get_entities so I think that there is even less reason to go your own way.

    Much better to put the effort into learning to do things the "Elgg way".

  • Stingbeans, it's sometimes worth creating a separate table for some data. It requires you to actually write a separate application that you later integrate with Elgg though - you can't use almost any Elgg mechanism. The only thing that can be used after rebuild are the pagination views.

    We've done this for data that is often searched by many parameters at once, that is shared between two or more platforms and/or requires to be more scalable. Most of the time you'll be better off with standard Elgg tables though.

  • I'm with Kevin on discouraging the use of your own tables.  Every time I've put in the effort to do something a non-Elgg way I eventually want to switch back to Elgg's way, including adding extra tables.

  • Thanks for the advice everyone! If I have a list of mainly static data, do you think that warrants an external table? Or should I write a script that populates elgg with that particular subtype (ie. countries)

  • For some kinds of static data, I'd be inclined to put it on the file system and just have my plugin load it when needed. Otherwise, it is quite trivial to write scripts to populate Elgg entities with additional metadata.

    One trick: when accessing more than a few dozen entities in a loop, I advise including:

    unset($DB_QUERY_CACHE);

    in the loop.

    Otherwise Elgg rapidly builds up a huge amount of data and PHP can run out of memory.

  • @Kevin LOLZ.. of course you have no idea why I had done that, despite knowing the Elgg API ;-) We had needed to interface into Elgg a user database (mysql) from an external application from a Windows server based application. There was no sense in trying to follow the "Elgg way". We simply allowed Elgg to access the non-elgg table and synchronize the users between Elgg and the external application to allow our users to bridge to the other application. And yes, but of course - within Elgg itself - everything was done the "Elgg way".

  • Dhrup, that makes perfect sense to me.

  • Thanks again for the feedback Kevin!

    One final thing... I've been trying to find some examples of how to use the 'joins' param for elgg_get_entities and i can't seem to find any. The current API documentation doesnt really give any examples. Do you think you could give me some examples of it? From the looks of it, it looks like I have to actually use a mysql JOIN string, but how will I know which tables I need to look at?