Q:

Can I have a condition inside of a where or a filter?

I have a dataframe with many columns, and to explain the situation, let's say, there is a column with letters in it from a-z. I also have a list, which includes some specific letters.
val testerList = List("a","k")

The dataframe has to be filtered, to only include entries with the specified letters in the list. This is very straightforward:
val resultDF = df.where($"column".isin(testerList:_*)))

So the problem is, that the list is given to this function as a parameter, and it can be an empty list, which situation could be solved like this (resultDF is defined here as an empty dataframe):
if (!(testerList.isEmpty)) {
 resultDF = df.where(some other stuff has to be filtered away)
                  .where($"column".isin(testerList:_*)))
} else {
  resultDF = df.where(some other stuff has to be filtered away)
}

Is there a way to make this in a more simple way, something like this:
val resultDF = df.where(some other stuff has to be filtered away)
                      .where((!(testerList.isEmpty)) && $"column".isin(testerList:_*)))

This one throws an error though:
 error: type mismatch;
 found   : org.apache.spark.sql.Column
 required: Boolean
                                    .where( (!(testerList.isEmpty)) && (($"agent_account_homepage").isin(testerList:_*)))
                                                                                                        ^

So, thanks a lot for any kind of ideas for a solution!! :) 

A:

What about this?
val filtered1 = df.where(some other stuff has to be filtered away)
val resultDF = if (testerList.isEmpty) 
  filtered1 
else 
  filtered1.where($"column".isin(testerList:_*))

Or if you don't want filtered1 to be available below and perhaps unintentionally used, it can be declared inside a block initializing resultDF:
val resultDF = {
  val filtered1 = df.where(some other stuff has to be filtered away)
  if (testerList.isEmpty) filtered1 else filtered1.where($"column".isin(testerList:_*))
}

or if you change the order
val resultDF = (if (testerList.isEmpty) 
  df
else 
  df.where($"column".isin(testerList:_*))
).where(some other stuff has to be filtered away)

A:

The solution I use now, use sql code inside the where clause:
var testerList = s""""""
var cond = testerList.isEmpty().toString
testerList = if (cond == "true") "''" else testerList

val resultDF= df.where(some other stuff has to be filtered away)
                .where("('"+cond+"' = 'true') or (agent_account_homepage in ("+testerList+"))")

What do you think?

