Beego: Support order by random()

[khanzadimahdi]

how can i order records by random using orm?

[flycash]

Do you use QuerySetter? It contains method:

    // add ORDER expression.
    // "column" means ASC, "-column" means DESC.
    // for example:
    //  qs.OrderBy("-status")
    OrderBy(exprs ...string) QuerySeter

[khanzadimahdi]

yes, however RAND() not works and generates error.

OrderBy("RAND()")

[flycash]

I think you can use QueryRaw

[khanzadimahdi]

@flycash
I want to filter my Model, using QueryRaw is not a clean way.

[flycash]

We will fix it in the future. We want to release v2.0.0-beta this weekend, and we don't have enough time to fix it. So it will be fixed in v2.0.1.

[khanzadimahdi]

so please add UpdateOrCreate method too.

[flycash]

There is a similar method InsertOrUpdate.

[khanzadimahdi]

the concept of insertOrUpdate is wrong!!! it means insert it and if it failed, update it!!!

i want the concept of update it, and if it failed, create it

it must be UpdateOrInsert

[flycash]

the concept of insertOrUpdate is wrong!!! it means insert it and if it failed, update it!!!

i want the concept of update it, and if it failed, create it

it must be UpdateOrInsert

Please correct me if I make some mistakes.

I think there is no SQL like Update xxx Or Insert. Actually, the InsertOrUpdate is implemented by using Insert xxx ON DUPLICATE KEY in MYSQL and Insert XXX ON CONFLICT (XXXX) DO UPDATE SET.

If we want to provide UpdateOrInsert, we need to start a transaction to do an update. And if we find affected rawnum is 0 and then we do an insert.

I think in most cases, insertOrUpdate is good.

So, could you tell me the scenario that you can't use insertOrUpdate?

[khanzadimahdi]

I asked for UpdateOrCreate method and you said me, use InsertOrUpdate.

UpdateOrCreate means ==> Update the record if exists, or create a new record.

You may come across situations where you want to update an existing model or create a new model if none exists.

[flycash]

You may come across situations where you want to update an existing model or create a new model if none exists.

Assume that we have a table named User. And you want to update User(name="Tom", email="[email protected]") to (name="New Tom", email="[email protected]"). And we think the email should be unique in the table. In this case, you can InsertOrUpdate. When we try to Insert new records, we find out there is a user whose email is "[email protected]", and then we update this user to set his name to "New Tom". If we cannot find any user whose email is "[email protected]", insert will be successful.

Those cases have similar features: There must be some unique id or unique index to let DB know whether two records are the "same" record. In the above example, we create unique index on "email" column. In those cases, "insert failed then update", or "update failed then insert", the final state of this record are the same.

In fact, the case you mentioned is the most common case that we use InsertOrUpdate.

[khanzadimahdi]

how about using non-unique fields? imagine we don't have any unique fields in our query

[flycash]

Sorry, in this case, I think you need to handle it manually. It's not a common case.

[flycash]

I kindly remind you that, if you are working on a distributed system, and you don't have any unique index, there are two problems that may cause an unpredictable result:

1. In general, "update failed then insert" is the "check-and-do-something" problem;
2. if you deploy multiple instances, what happens if more than two instances "update-or-insert"? It's affected by DB's isolation level;