Querying Databases

Posted in

#76 by NaioHoras
2021-05-31 at 01:31
< report >I don't know if this is what they call programming tricks or this query link really has nothing to do with VN releases...
#77 by Yorhel
2021-05-31 at 06:16
< report >Seems like (some?) queries saved before SQLpad added the fine-grained permissions system can be edited by everyone. I'll lock mine down...
#78 by rampaa
2021-07-02 at 17:49
< report >Just curious, what's wrong with link (d18.9)?
#79 by Yorhel
2021-07-02 at 17:56
< report >What's wrong is that I failed at copy-pasting the URL to test if it still worked, lol. But yeah, it does work after all.
#80 by uvix
2021-07-05 at 20:34
< report >I am no longer able to save queries, even without any changes; it's just returning HTTP 500.
#81 by Yorhel
2021-07-06 at 06:01
< report >Works for me. Does the t12800.72 trick work?
#82 by uvix
2021-07-08 at 03:12
< report >It does. That's strange, I'm pretty sure that I've saved queries between then and now... Well, at least I have a workaround. Thanks!
#83 by NaioHoras
2021-07-29 at 02:19
< report >based on t8242.681, I'd like to request a query for VN title with different name with its first complete release if possible
#84 by foiegras
2021-07-29 at 05:31
< report >@83
It's far from perfect, but it's a start.Last modified on 2021-07-29 at 05:31
#85 by Ileca
2021-07-29 at 05:44
< report >link

Enjoy. You get a shit-ton of results.
Any idea why I can't use POSIX? I tried
WHERE LOWER(r_title) !~ ((LOWER(vn_title) || '.*'))
but I get an error. It appears to be because of vn_title but I don't know why.

Edit: got ninja'd apparently. Motemote da na, naiohoras... :(Last modified on 2021-07-29 at 05:46
#86 by NaioHoras
2021-07-29 at 07:35
< report >holy shit, more results than I expected, the fewer one is at least 1346...
anyway, thanks you two!
#87 by NaioHoras
2021-07-29 at 07:51
< report >just realized that some difference in vn main title and first complete release title is because a vn is included in some kind of "pack" release (such as r2600 and r947), which I believe it's exception to the rule, and there are actually a lot of them in both queries.... is it possible for them to exclude "pack" releases?Last modified on 2021-07-29 at 07:59
#88 by wyldstrykr
2021-08-07 at 14:08
< report >now that theres a voting thingy on the vns length, is the field vn.length affected in a way?
#89 by Yorhel
2021-08-08 at 07:56
< report >
is the field vn.length affected in a way
Not right now. In the future, I expect it'll be removed and replaced with a cached column indicated the aggregate play time - possibly seeded from the old vn.length if there are no votes. The raw play time votes can already be queries through the vn_length_votes table.
#90 by NaioHoras
2021-12-03 at 11:42
< report >every query with r.type does not work now (including the ones here d18). pretty sure it could work if I replace it with something else but I don't what...
#91 by Yorhel
2021-12-03 at 17:01
< report >releases.type has moved to releases_vn.rtype. I've fixed the queries on d18.
#92 by NaioHoras
2022-03-31 at 10:07
< report >I'd like to request two queries:
- VN with alternative title but with no release in that language.
- VN with alternative titles which use different script from latin, but no romanization.

thanks in advance
#93 by foiegras
2022-04-02 at 16:22
< report >@92
Here is the first one: link
I've added a filter to mix Chinese and Portuguese variations together, though it can be removed.

And the second one: linkLast modified on 2022-04-02 at 16:31
#94 by NaioHoras
2022-04-03 at 03:29
< report >once again, thank you!
#95 by Duskit
2022-05-26 at 00:21
< report >I made a query that weighs: tag preference/b_rating/age, 60%/20%/20%
For anyone curious or would find it useful.
It was made more through trial and error so it probably could have been done better. link


You must be logged in to reply to this thread.