Create condition that searches between two different WHEREs in the table-CodePudding

I'm making a little game where robots fight each other. The robots have a table in the database where their match scores are recorded. Each robot has its own home island where it can face opponents. The opponents will have their own island. Each robot has its own island. The question is simple enough, but it could lead to confusion, so I briefly explain step by step:

EXPLANATION

1. I select the name of a particular robot from the combobox

if robot_her_island:
select_robot_on_her_island = select_war.split('-')[0]

2. Using Last_War_on_any_island I search for the last war of the selected robot. I look for either the war that took place on your own island or on the island of your opponents. I only select the last war

#Last War on ANY island
cursor.execute("SELECT robot_on_her_island, robot_on_island_away, points_war_home, points_war_away FROM War WHERE robot_on_her_island =? OR robot_on_island_away=? LIMIT 1", [select_robot_on_her_island, select_robot_on_her_island])
Last_War_on_any_island = cursor.fetchall()[0]

3. I create two separate cursor.execute: one for when a robot fights on its own island and another when a robot fights on an island not its own. How come? Because the problem might seem solved like this in point 2, but it's okay only when the war is between robot_on_her_island VS robot_on_island_away (the two data in the database) and then the corresponding points are points_war_home and points_war_away. But when the war is backwards, i.e. robot_on_island_away VS robot_on_her_island, then I should also reverse points_war_away-points_war_home, because I'm trying to get information about a certain robot (selected in the initial combo box).

Consequently, to solve this, I thought about creating two separate cursor.execute, one for when a robot is fighting on its own island (Last_War_on_her_island) and another when a robot is fighting off on an island not its own (Last_War_on_island_away). This way I can also swap points_war_home and points_war_away (on the contrary, by reversing the order)

I give an example of point 3:

MAZINGER VS GOLDRAKE robot_on_her_island VS robot_on_island_away = points_war_home, points_war_away (Mazinger, Goldrake = 2-1)

GOLDRAKE VS MAZINGER robot_on_her_island VS robot_on_island_away = points_war_away, points_war_home (Goldrake-Mazinger = 3-2) #invert points

#Last War in robot home island
cursor.execute("SELECT points_war_home, points_war_away FROM War WHERE robot_on_her_island =? LIMIT 1", [select_robot_on_her_island])
Last_War_on_her_island = cursor.fetchall()[0]

#Last War in robot away island (robot island opponents)
cursor.execute("SELECT points_war_away, points_war_home FROM War WHERE robot_on_island_away =? LIMIT 1", [select_robot_on_her_island])
Last_War_on_island_away= cursor.fetchall()[0]

Good! Now I have the last robot_on_her_island war, and the last robot rival robot_on_island_away war. Perfect, but the problem is right here!

QUESTION

How do I know if the last robot war that I initially selected from combobox was carried out on its own island or on that of some other robot?

The answer is the cursor.execute I used above called Last_War_on_any_island, but I don't know how to put the code together. Let me explain: having Last_War_on_any_island which serves to find out what was the last general war of the robot that I selected with the combobox, then using Last_War_on_any_island I would like to be able to get Last_War_on_her_island or Last_War_on_island_away

FOR EXAMPLE I WANT TO GET THIS:

For example, if I choose Daitarn in the combobox, the last war of Daitarn will be sought, which is the one in id 2 (Jeeg-Daitarn). Then Last_War_on_island_away will be printed. While if I had chosen Mazinger in the combobox, Mazinger's last war would have been in id 1 (Mazinger-Goldrake) and therefore Last_War_on_her_island would have been printed. IMPORTANT: When I say "last" I refer to the last time the robot NAME is displayed in the tab (indifferently from Home or Away, or ID), but only the last time the name is present

CODE COMPLETE

    if robot_her_island:
    
    robot_her_island = select_war.split('-')[0]      
    
    #Last War on ANY island
    cursor.execute("SELECT robot_on_her_island, robot_on_island_away, points_war_home, points_war_away FROM War WHERE robot_on_her_island =? OR robot_on_island_away=? LIMIT 1", [select_robot_on_her_island, select_robot_on_her_island])
    Last_War_on_any_island = cursor.fetchall()[0]
    
    #Last War in robot home island
    cursor.execute("SELECT points_war_home, points_war_away FROM War WHERE robot_on_her_island =? LIMIT 1", [select_robot_on_her_island])
    Last_War_on_her_island = cursor.fetchall()[0]
    
    #Last War in robot away island (robot island opponents)
    cursor.execute("SELECT points_war_away, points_war_home FROM War WHERE robot_on_island_away =? LIMIT 1", [select_robot_on_her_island])
    Last_War_on_island_away= cursor.fetchall()[0]

    #THE PROBLEM IS HERE
    if Last_War_on_any_island is Last_War_on_her_island:
        print(Last_War_on_her_island)
    else
        print(Last_War_on_island_away)

CodePudding user response：

You can do the test for whether it's home or away in the query itself.

cursor.execute("""
    SELECT
        CASE WHEN robot_on_her_island = ? THEN 'Last_War_on_her_island'
             ELSE 'Last_War_on_island_away'
        END AS which_island,
        points_war_home, points_war_away
    FROM war
    WHERE ? IN (robot_on_her_island, robot_on_island_away)
    LIMIT 1""",
    (select_robot_on_her_island, select_robot_on_her_island))
row = cursor.fetchone()
if row:
    which_island, points_home, points_away = row
    if which_island == 'Last_War_on_her_island':
        print(which_island, points_home, points_away)
    else:
        # Swap the points when won on the away island
        print(which_island, points_away, points_home)

BTW, if you just want one row, use fetchone() rather than fetchall()[0].