Re: Select Fields Not Empty (nil)
Hi. Sorry for the delay. Fighting a now 4-week cold, been out of commission. Been reading and testing some more, translating my current projects. I try to post generalized examples as this isn't about anything specific. -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/1da11bff-dca0-45ef-916b-4c3c896f680b%40googlegroups.com.
Re: Select Fields Not Empty (nil)
On Wednesday, January 8, 2020 at 9:33:55 AM UTC-8, BeeRich33 wrote:
>
>
>> My apologies for correcting you yet again, hence the questions here in
> the group meant for such questions:
>
> DBS[:searches_t].
> where{(exclude(search_phrase: nil))}.
> select(:search_phrase, :result_count)
>
>
> ERROR. *HINT: No function matches the given name and argument types.
> You might need to add explicit type casts. *Points to "exclude"
>
> The "Where TRUE" is what the .sql is explaining. From here:
>
> where{(exclude(:search_phrase = nil) & (:client_ip != '192.168.1.4')}.
>
>
This doesn't work for multiple reasons. I don't even think it is valid
ruby syntax (:search_phrase = nil). Even if you fixed the Ruby syntax
issues, you don't want to call exclude inside where, as it isn't an SQL
function, and :client_ip != '192.168.1.4' is always going to be true, since
a Ruby symbol is not ever equal to a Ruby string.
> Question still not answered. I've simplified the query to the following,
> which still has no answer:
>
> DBS[:searches_t].
> where{(exclude(search_phrase: nil))}.
> select(:search_phrase, :result_count)
>
Here you are using the virtual row syntax to call the exclude SQL function,
except that exclude is not an SQL function. You want to call
exclude(search_phrase: nil) instead of where{(exclude(search_phrase: nil)
I've also tried:
> DBS[:searches_t].
> where{(:search_phrase != nil)}.
> select(:search_phrase, :result_count)
>
> => provides nils. So that doesn't work.
>
A Ruby symbol will never equal nil. You could possibly use
where{search_phrase !~ nil}, though the exclude example given above is more
idiomatic.
>
> I've also tried:
>
> DBS[:searches_t].
> where{(:search_phrase !~ nil)}.
> select(:search_phrase, :result_count)
>
> => provides nils. Doesn't work.
>
Sequel doesn't override Symbol#!~ (or any core Ruby methods by default).
(:search_phrase
!~ nil) returns true, so the where call is the same as where{true}
> I'm reading as much as I can, and wish to avoid coming in here. Believe
> me.
>
While the documentation covers the issues you have brought up, it does not
directly address your particular use case. The documentation does assume
an intermediate level understanding of the Ruby language, so it doesn't
directly explain why only one of the following gives you the results that
you want:
where{:search_phrase != nil}
where{:search_phrase !~ nil}
where{search_phrase != nil}
where{search_phrase !~ nil}
In short, :search_phrase and search_phrase are different here, with
:search_phrase being a Ruby symbol and search_phrase being a method call
that returns a Sequel-specific object. That object implements !~ but not
!=, as overriding != would break Ruby-level equality.
You should be aware that this is a help forum for the Sequel library, not a
forum to help you with your specific project. You should try to keep your
requests for help generic (unrelated to your project), and as minimal as
possible for the topic you have a question about. It would probably be
best if you could always post the SQL you are trying to generate, and ask
how to represent it in Sequel, as that minimizes the potential problems.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/1622db1f-81b8-412c-8bdf-54514bd648fd%40googlegroups.com.
Re: Select Fields Not Empty (nil)
On Wed, Jan 8, 2020 at 9:33 AM BeeRich33 wrote:
>
>> My apologies for correcting you yet again, hence the questions here in
> the group meant for such questions:
>
> DBS[:searches_t].
> where{(exclude(search_phrase: nil))}.
> select(:search_phrase, :result_count)
>
>
>
Please go here
http://sequel.jeremyevans.net/rdoc/files/doc/dataset_filtering_rdoc.html#label-Negating+conditions
And look at the first example - you are combining concepts.
You want
DBS[:searches_t].
exclude(search_phrase: nil).
select(:search_phrase, :result_count)
That should pop out an IS NOT NULL for you.
I believe you would be fully looking for
DBS[:searches_t].
exclude(search_phrase: nil).
exclude(client_ip: '192.168.1.4').
select(:search_phrase, :result_count)
John W Higgins
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CAPhAwGzr4MpMpGp4vDva97OUUfUH7UwaORgUtVon10fAYSMiug%40mail.gmail.com.
Re: Select Fields Not Empty (nil)
>
>
> My apologies for correcting you yet again, hence the questions here in the
group meant for such questions:
DBS[:searches_t].
where{(exclude(search_phrase: nil))}.
select(:search_phrase, :result_count)
ERROR. *HINT: No function matches the given name and argument types. You
might need to add explicit type casts. *Points to "exclude"
The "Where TRUE" is what the .sql is explaining. From here:
where{(exclude(:search_phrase = nil) & (:client_ip != '192.168.1.4')}.
Question still not answered. I've simplified the query to the following,
which still has no answer:
DBS[:searches_t].
where{(exclude(search_phrase: nil))}.
select(:search_phrase, :result_count)
I've also tried:
DBS[:searches_t].
where{(:search_phrase != nil)}.
select(:search_phrase, :result_count)
=> provides nils. So that doesn't work.
I've also tried:
DBS[:searches_t].
where{(:search_phrase !~ nil)}.
select(:search_phrase, :result_count)
=> provides nils. Doesn't work.
I'm reading as much as I can, and wish to avoid coming in here. Believe
me.
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/37e2cf0c-5960-4ed9-83f2-5749d04f9f27%40googlegroups.com.
Re: Select Fields Not Empty (nil)
Respectfully in order
On Wed, Jan 8, 2020 at 8:56 AM BeeRich33 wrote:
> You need to ask questions before accusing people. That is plain outright
> rude.
>
> See, there are obviously multiple websites. Did you know that? I spend
> lots of time reviewing here:
>
> http://sequel.jeremyevans.net/rdoc/files/doc/dataset_filtering_rdoc.html
>
> So have some respect. I'd love to not wait for anybody to get back to
> me.
>
> Nowhere on those pages, does it say anything about not returning empty
> fields.
>
> DBS[:searches_t].
> where{(:search_phrase != nil) & (:client_ip != '192.168.1.4')}.
> select(:search_phrase, Sequel.cast(:creation_date,
> DateTime).as(:creation_date), :search_type, :result_count).
> reverse(:creation_date).
> limit(d).sql
>
> Doesn't resolve properly.
>
Going to the page you linked - there are 4 instances of != - specifically
in a section entitlted - "Negating conditions" - each one of which explains
exactly how to accomplish this.
Nil is mentioned 4 times on that page - again showing exactly how to get
the IS NULL statement - add in the negation concept and you are in business.
so from that page
.exclude(search_phrase: nil).exclude(client_ip: '192.168.1.4')
which equates to
where search_phrase is not null and client_ip != '192.168.1.4'
Nowhere in the documents does it say anything about this:
>
> SELECT "search_phrase", CAST("creation_date" AS timestamp) AS
> "creation_date", "search_type", "result_count" FROM "searches_t" WHERE true
> ORDER BY "creation_date" DESC LIMIT 30
>
>
> "WHERE true"?
>
>
"Where TRUE" is unnecessary. One wouldn't document how to add something
that you don't need. You simply don't write that - your statement works as
this.
SELECT "search_phrase", CAST("creation_date" AS timestamp) AS
"creation_date", "search_type", "result_count" FROM "searches_t" ORDER BY
"creation_date" DESC LIMIT 30
However, if you would like that string - I believe the section entitled
"Filtering using a custom filter string" would lead you to something like
this
.where(Sequel.lit('true')
My apologies for trying to point you in the right direction.
John W Higgins
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CAPhAwGw0dH%3DvSucbLRVmAO53QLONdqhTYwP%2Bmzst%3D8Ra1%3D5M-w%40mail.gmail.com.
Re: Select Fields Not Empty (nil)
Just reviewed it yet again.
>
>
.invert: Nothing to invert. What is an invert of 'field with something'?
.exclude 'any field value'? The field values I want to avoid are *nil*,
not ''.
'not in' doesn't apply as there's no qualifier set.
'not like' doesn't apply.
Sequel.~ throws an error, "WHERE NOT 'search_phrase'..."
~Sequel. is not applicable.
Negated expressions, !~:
where{(:search_phrase !~ nil)}.
...returns nil search phrases, so this doesn't work nor apply.
And that's it. The complete section you just "hinted" about.
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/d26f699a-6332-44b9-8186-889bb629dcb4%40googlegroups.com.
Re: Select Fields Not Empty (nil)
You need to ask questions before accusing people. That is plain outright
rude.
See, there are obviously multiple websites. Did you know that? I spend
lots of time reviewing here:
http://sequel.jeremyevans.net/rdoc/files/doc/dataset_filtering_rdoc.html
So have some respect. I'd love to not wait for anybody to get back to me.
Nowhere on those pages, does it say anything about not returning empty
fields.
DBS[:searches_t].
where{(:search_phrase != nil) & (:client_ip != '192.168.1.4')}.
select(:search_phrase, Sequel.cast(:creation_date,
DateTime).as(:creation_date), :search_type, :result_count).
reverse(:creation_date).
limit(d).sql
Doesn't resolve properly. Nowhere in the documents does it say anything
about this:
SELECT "search_phrase", CAST("creation_date" AS timestamp) AS
"creation_date", "search_type", "result_count" FROM "searches_t" WHERE true
ORDER BY "creation_date" DESC LIMIT 30
"WHERE true"?
Show me where it's explained. If you can't, then please understand this is
a forum for people looking for answers. If you don't like it, then you
don't have to be here. Maybe you should read the docs. The question is
valid. Maybe you should be respectful and read the question.
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/fa442373-1a81-4267-9dbe-361a16829e6d%40googlegroups.com.
Re: Select Fields Not Empty (nil)
On Wed, Jan 8, 2020 at 8:23 AM BeeRich33 wrote:
> I can't seem to get a select to work on non-empty fields:
>
> where{(:search_phrase != nil) & (:client_ip != '192.168.1.50')}.
>
>
You really do need to at least attempt to read the docs. Jeremy has spent a
lot of time writing things like [1] which completely answer your question
here (hint - inverting).
Some attempt to be respectful would seem to be in order here.
John W Higgins
[1] - https://github.com/jeremyevans/sequel/blob/master/doc/querying.rdoc
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CAPhAwGwS21EmxQ59t4t2tN30Nie-tXWBTNBrM_RWuHspdXOrfw%40mail.gmail.com.
Select Fields Not Empty (nil)
I can't seem to get a select to work on non-empty fields:
where{(:search_phrase != nil) & (:client_ip != '192.168.1.50')}.
Tried this as well:
where{(:search_phrase != '') & (:client_ip != '192.168.1.4')}.
PostgreSQL registers the field as *null*, but searches come back with *nil*
as field values.
How can I eliminate rows with empty fields? Cheers
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/3d150a88-6584-4d08-b24f-90683dc156ae%40googlegroups.com.
