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.


You must be logged in to reply to this thread.