Added language_id to a search

[tfnribeiro]

• With the new subscription system if a user changes languages they would still see all the keywords that aren't really relevant for that language.
• This change seeks to store the language_id that the users were learning when they performed the search and they will only be retrieved if the user has that language set as current language.

I have tested this with a few languages in my local environment and it seems OK. However, I haven't got all the searches as I did some search deletion and as such there wasn't a lot of issues running the script.

Some situations to look out for is people subscribed / filtering the same keyword with different languages, who do we pick? Do we just delete these and they can re-add it at some point?

[github-actions[bot]]

Module view diffs:

[mircealungu]

i guess we should migrate to MariaDB at some point:

https://aws.amazon.com/compare/the-difference-between-mariadb-vs-mysql/

support for mysql has ended end of last year ^^

https://mariadb.com/resources/blog/migrate-to-mariadb-server-from-mysql-5-7-using-a-single-command/

m.

On 26 Sep 2024 at 10.40 +0300, Mircea Filip Lungu @.***>, wrote:

On 26 Sep 2024 at 10.37 +0300, Tiago Ribeiro ***@***.***>, wrote: > @tfnribeiro commented on this pull request. > In tools/migrations/24-09-24--adding-language-to-search.sql: > > @@ -0,0 +1,30 @@ > +ALTER TABLE > + `zeeguu_test`.`search` > +ADD > + COLUMN `language_id` INT NULL > +AFTER > + `id`, > + CHANGE COLUMN `keywords` `keywords` VARCHAR(100) NULL DEFAULT NULL, > +ADD > + INDEX `search_ibfk_1_idx` (`language_id` ASC) VISIBLE; > I am just using MySQLs workbench so that's just the default - which version of mysql is in the server? > — > Reply to this email directly, view it on GitHub, or unsubscribe. > You are receiving this because you modified the open/close state.Message ID: ***@***.***>

[mircealungu]

I have tested this with a few languages in my local environment and it seems OK. However, I haven't got all the searches as I did some search deletion and as such there wasn't a lot of issues running the script.

the migration script worked well on the server as far as I can tell.

Some situations to look out for is people subscribed / filtering the same keyword with different languages, who do we pick? Do we just delete these and they can re-add it at some point?

I'm not sure I understand this ^^ Is this something that could have happened during the migration? or is it for the future?

[tfnribeiro]

Well it could happen in Migration - essentially users could have the same keyword but different languages, which means the join would probably return 2 rows with the same search, I am not sure what happens in that situation. I am guessing the join would return two rows, e.g.:

search_id	keyword	language_id
1	Trump	2
1	Trump	5

I guess in this case the row with language_id = 5 would be saved. So some users might have lost their search because it was associated with a different language.

In the future, this won't happen because when you make your search, it will be stored for that language_id and you are mapped to that search_id.

[tfnribeiro]

Now that I think about it, it could have also been implemented at the filter / subscription level.

As it is now, Trump is a different search for Danish and French for example. If we were to store it at the filter and subscription level then search would be the same id for french and danish users - and are only distinguished on the filter / subscription level.

[tfnribeiro]

i guess we should migrate to MariaDB at some point: https://aws.amazon.com/compare/the-difference-between-mariadb-vs-mysql/ support for mysql has ended end of last year ^^ https://mariadb.com/resources/blog/migrate-to-mariadb-server-from-mysql-5-7-using-a-single-command/ m. … On 26 Sep 2024 at 10.40 +0300, Mircea Filip Lungu @.>, wrote: On 26 Sep 2024 at 10.37 +0300, Tiago Ribeiro @.>, wrote: > @tfnribeiro commented on this pull request. > In tools/migrations/24-09-24--adding-language-to-search.sql: > > @@ -0,0 +1,30 @@ > +ALTER TABLE > + zeeguu_test.search > +ADD > + COLUMN language_id INT NULL > +AFTER > + id, > + CHANGE COLUMN keywords keywords VARCHAR(100) NULL DEFAULT NULL, > +ADD > + INDEX search_ibfk_1_idx (language_id ASC) VISIBLE; > I am just using MySQLs workbench so that's just the default - which version of mysql is in the server? > — > Reply to this email directly, view it on GitHub, or unsubscribe. > You are receiving this because you modified the open/close state.Message ID: @.***>

Doesn't this only apply to version 5.7?

https://endoflife.date/mysql

[mircealungu]

I thought I mentioned it above, but I don't find it in the discussion anymore: we're using 5.7.44 MySQL Community Server. So yeah, we're beyond the EOL

[mircealungu]

Let's find out what's easier to upgrade to: Maria or 8.0... 8.0 will also give us four more years of support. And Maria seems to be even faster in some use-cases. And it's more open than Oracle's..

[tfnribeiro]

I thought I mentioned it above, but I don't find it in the discussion anymore: we're using 5.7.44 MySQL Community Server. So yeah, we're beyond the EOL

That will explain why there will be conflicts with the SQL generated I feel!

Let's find out what's easier to upgrade to: Maria or 8.0... 8.0 will also give us four more years of support. And Maria seems to be even faster in some use-cases. And it's more open than Oracle's..

To me it's fine, I just wasn't sure of the version we had and if the migration is straight forward I don't see why not. My intuition is that we aren't using many features of MySQL, it's just a database so it shouldn't cause too many headaches.