Convert ids to values without using too many joins

[eluser]Unknown[/eluser]
I am using the below query in my model

Code:

select id, name, status from list

here status is an integer field and the value of status is on refdata table

I know that I could use join to get the value of status for view to display. But I would like to use a lookup array which will be refreshed periodically from the database for getting the value of status. Reason is performance and I will need to join refdata many many times with the list table for getting other field values.

I appreciate any help on this.

[eluser]rogierb[/eluser]
You can use a helper where you load the refdata in an array. But only if the array does not exist. If it exists, do nothing.

Code:

if(!isset($CI->refdata))
{
//load from database into $ci->refdata
}
else
{
//do nothing, you can use the array $this->refdata
}

This way you only load the data once per page request.

Another way is to (re)create a config file with a conjob script.

[eluser]jedd[/eluser]
Hi planmatrix and welcome to the CI forums.

[quote author="planmatrix" date="1259235584"]

Code:

select id, name, status from list

here status is an integer field and the value of status is on refdata table

I know that I could use join to get the value of status for view to display. But I would like to use a lookup array which will be refreshed periodically from the database for getting the value of status. Reason is performance and I will need to join refdata many many times with the list table for getting other field values.
[/quote]

Answering the question as posed, I see a couple of ways forward.

Pull the refdata table in and store it in session data - this works well if it's very small and you're not using database-backed sessions. Actually it might not work all that well, as it's going to have to consult a cookie on each lookup - and that may be expensive (not sure of the network implications).

Pull the refdata table in, serialize() it, and store the output into a file and then pull that file in & deserialize it on each subsequent page request.

Both these approaches require that you have some mechanism for establishing if the secondary store of your refdata is stale, and of course a method for storing / refreshing it.

To answer the question behind the one you posed ... DON'T DO THIS. And here's a few reasons why I say this.

It's really ugly. It's a lot more code for very little (perhaps no) measurable gain. Writing more code means more code to maintain and more bugs to fix.

It attempts to solve a problem you don't have - aka premature optimisation. Absent any testing done by yourself on the two approaches (JOIN or secondary store) you're working in the dark to fix a problem that, as far as you know, doesn't exist.

It ignores the work of lots of people much smarter than us - the guys that wrote the caching systems for the operating / file system, and your database. These are way more efficient than any bit of PHP you reckon you can rattle up that tries to second guess the database.


So .. stick with the JOIN now, and later on if you find the performance is not up to scratch and by some miracle you identify this one JOIN is causing the majority of your performance woes .. then you should re-engineer this bit of your app.