While implementing the code from this question on my project I realized there's 3 way I can accomplish something but I have no idea which one is better. Is there a performance again using one over the other? Is that performance worth the more complicated implementation? That's the kind of stuff I want to know.
Although I did not specify, that question was about selecting a movie info from a table and join every genre in a single column separated by commas. Now, I need those genres to be clickable in my code so I can filter by the clicked genre.
I thought of 3 ways to do this:
Solution 1)
Use 1 SELECT query and the function on that other question than use PHP to build the HTML string. Something like this:
$genres = explode(',', $info['GENRES']);
$count = count($genres);
for($i = 0; $i < $count; $i++) {
$genres[$i] = '<a href="#">'.$genres[$i].'</a>';
}
$info['GENRES'] = implode(' | ', $genres);
Then I realized I'm missing the genre ID so I can easily create a link to filter by that genre and I thought of changing the Oracle function to include it. How? By creating a JSON string (ie: {"1":"Action","2":"Crime","5":"Thriller"}, the function can easily be modified for this) and it's a simple matter of adapting the code above accordingly.
Solution 2)
Use 2 SELECT statements and completely ignore the function on that other question. One query to select the movie info, the other to select all genres (id and name) associated to that movie. Then PHP would do the rest to build the HTML code string.
Solution 3)
Simply use 1 SELECT query and the Oracle function adapted to construct the whole string by itself. Meaning, the PHP code would be as simple as executing the SQL statement. The Oracle function would prepare the whole HTML string.
Now, this solution may not work as I really need it, rendering it irrelevant. But I don't know that cause I'm not sure how exactly I'm going to build those HTML links to filter by genre, haven't really thought about it yet. Well, in PHP I can always do it, one way or another, not sure I can do the same in an Oracle function.
Still, let's assume it's possible, I believe it is.
Which solution should I go for?
Is there any reason, in this situation, for the Oracle function to do all the work (or vice-versa)? Given this, should I go with solution 3 or solution 1/2?
If solution 1/2 is the better way, which one should I go for? Solution 1 has 1 SELECT in the PHP code but another in the Oracle function. Solution 2 has 2 SELECTs in the PHP code. Is there a performance gain on one over the other or they are basically the same? If so, which one is better?
Solution 1 has a simpler PHP code but introduces a slightly more complicated code for the Oracle function. In the other hand, solution 2 has no Oracle function code at all and the PHP code will increase in size/complexity, not by much though.
I'm inclined towards solution 1, but would love to hear your thoughts.
