How column value inside select option

@lbecket I have tryed your method but it didnt fix my problem. I think i was not able to retrive data from query on field (hard-coded). So i found another solution make it work from frontend. I have add index for each isHidden model and it work.

This is a project with some complications and for me it is the first time that I am working with a dynamic table in such a way. So I have to copy a table from mysql2 to mysql1 and to do this I have to copy the entire structure of myql2 to mysql1, therefore I also need the function that adds the fields (schema) to the table. But I must also have functions like filter search hide and show column in the mysql1 table. edit single record delete, mass delete. This is the description of the project

I'm happy to try to help you through this

Thank you i appriciated your help

@Leon012 I don't see how these factors invalidate my approach. Whether the fields object is hard-coded or set dynamically is irrelevant. As I mentioned, you need to identify your list of fields somehow. Perhaps you have to tackle this in two stages. When you mount the component, query the table for all available fields, then take the results and assign them to the fields object and run another query to get your leads.

@lbecket

hard-coded or set dynamically

There is the problem i dont know how to set field dynamically in your previews example. Maybe I haven't mentioned it before but I'm new to vue js and this is my first time doing this

@Leon012 If you take the approach of using two queries, the first to identify your fields and the second to query your leads based on those fields, then your vue component could look something like this:

mounted() {
    axios.get('/fieldNames').then(r => (
        this.fields = r.data;

        this.getData();
    ));
}

This assumes that you have a getData() method similar to what I had illustrated here.

The query to identify the fields could be part of the getData() method, but then you have to call it every time instead of just once when the component is first mounted. Maybe that's what you want, maybe it's not.

If you take my approach, the initial query to get your fields will also have to assign them a default visibility. In other words, you're not returning a list of just field names, but a data structure that is consistent with what I had hard-coded.

@lbecket

your fields will also have to assign them a default visibility.

I think i should set the visibility on backend(laravel controller) am i right? I am trying to do something like this

data(){
            headers: [],
            fields: [],
            leads: [],
			....
}
mounted() {
        axios.get('/leads/fieldNames').then(r => {
            this.fields = r.data;

            this.getData();
        });

        this.getData()
}

getData() {
            axios.post('/leads/getleads', {
                fields: this.fields
            })
            .then(response => {
                this.leads = response.data;

                this.headers = this.fields.map(item => {
                    if (item.visible) {
                        return item.alias;
                    }
                });
                this.loading = false
            });
        },

also on conroller

public function getleads(Request $request)
    {
        $fields = collect($request->fields)
            ->where('visible', true)
            ->pluck('name')
            ->implode(',');
            
        if ($fields != "") {
            return Lead::selectRaw($fields)->get();
        }

        return [];
    }

    public function fieldNames(Request $request)
    {
// I think i need to modify this query

        $paginate = $request->perPage;
        $queryResults = Lead::paginate($paginate);
        $headers = collect($queryResults->first())->keys();

        return [$headers];
    }

with this try i get empty table. But on network tab i see this

//for fieldName 
0: ["id", "first_name", "last_name", "primary_email", "primary_phone", "lead_status", "notes",…]

//for getleads first request payload:
{fields: []}
fields: []

//for getleads second request payload:
{,…}
fields: [["id", "first_name", "last_name", "primary_email", "primary_phone", "lead_status", "notes",…]]

@Leon012 A few things...

• mounted() is a vue lifecycle hook, not a method
• Don't call getData() a second time within mounted(), you only want to call it once after the initial axios call has returned its promise.
• You can set a default visibility on the back end, but then you'll want to manage that state within the vue component. My example does this, you just have to set an initial value for the fields object based on that query in your mounted() hook.

@lbecket

is a vue lifecycle hook, not a method

I know this thing but i just see your example and copied it. but i can create a method and call on mounted

You can set a default visibility on the back end ...

How exactly can i do this?

You mean this exalmple?

fields: [
					{name: 'first_name', alias: 'First Name', visible: true},
					{name: 'last_name', alias: 'Last Name', visible: true},
					{name: 'email', alias: 'Email', visible: false},
				]

@Leon012 A vue lifecycle hook is not a method, but methods can be called within a hook. I misinterpreted your code since you didn't represent the getData() method within methods: {}.

How exactly can i do this? You mean this exalmple?

Sure. The objective is to assign your dynamically generated fields to the fields object, which in this case has three properties: name, alias, and visible. The idea is that the structure returned from the back end would mimic this. If it doesn't, then you would need to transform it on the front end. And to be clear, you don't need to follow this structure exactly, this was just my interpretation of the need. The alias property, for example, was a way to make your table headers more readable than what the database field name might be. You don't have to take that approach, but just know that if you remove the alias that you'll have to update the corresponding reference to it in the table component.

@lbecket Now it's getting a little more confusing, I shouldn't follow your advice to have 2 different requests (one for field and one for data). and this brings me back to my method that I used in the beginning getData() which returns two given headers and leads. Does this mean that I don't need ``fields''? Sorry if I'm asking too many questions but this is a bit confusing for me. What I want is to call the structure of the table as it is in the db and create some functions such as `edit, delete, export, with a few words lead management' one of these functions is the filter where I am currently stuck because the others I have solved most of it, but with the method i have posted on my old thread

f you remove the alias that you'll have to update the corresponding reference

i thnik there is no referece key what i can call to display field

fields: [["id", "first_name", "last_name", "primary_email", "primary_phone", "lead_status", "notes",…]]
0: ["id", "first_name", "last_name", "primary_email", "primary_phone", "lead_status", "notes",…]
0: "id"
1: "first_name"
2: "last_name"
3: "primary_email"
4: "primary_phone"
5: "lead_status"
6: "notes"
7: "created_at"
8: "updated_at"

@Leon012 If it's getting more confusing, then I'd suggest that you are introducing unnecessary complexity. I've offered a working template and it's pretty straightforward.

I shouldn't follow your advice to have 2 different requests

Fine, but how are you going to identify the fields that you need to pass to the query that retrieves your leads data? You have assured me several times that there is no way to know what the fields are at the start, so I can think of no other way to identify them than to query the table. If you don't know them, then you have to get them.

@lbecket

I shouldn't follow your advice to have 2 different requests

I think I misunderstood your previous answer and in fact this was a question.

And I've tried to follow your examples, but I haven't achieved what I'm looking for, if you have the opportunity you can make an example in the code according to the advice given to me, that the fields are also taken dynamically

@Leon012 The code below assumes that you're querying an Eloquent model named Lead. Obviously, you will need to adjust this to hit your actual table.

I've also configured this for the possibility that you may know of some fields that you would never want to return and so those would reside in the $fieldsToIgnore array. Also, since you presumably don't know which fields exist, all fields are visible by default.

In your vue component:

data() {
	return {
		leads: [],
		headers: [],
		fields: [],
	}
},

methods: {
    getData() {
        axios.post('/getLeads', {fields: this.fields}).then(response => {
            this.leads = response.data;

            this.headers = this.fields.map(item => {
                if (item.visible) {
                    return item.name;
                }
            });

        });
    },
},
								
mounted() {
    axios.get('/getFields').then(r => (
        this.fields = r.data.map(field => {
		    return {
			    name: field,
				visible: true
			}
		});

        this.getData();
    ));
}

In your controller:

public function getFields()
{
    $fieldsToQuery = collect(Lead::first())->keys()->toArray();
    $fieldsToIgnore = ['id', 'created_at', 'updated_at'];
   return array_diff($fieldsToQuery, $fieldsToIgnore);
}

public function getLeads(Request $request)
{
    return Lead::selectRaw($request->fields)->get();
}

This code is untested, but assumes that you'll be integrating it with the template that I provided earlier. Once mounted, the visibility of a given field is managed by the check boxes in the component.

@lbecket now it show error on console

Uncaught (in promise) TypeError: r.data.map is not a function

By the way i have replaced

axios.get('/getFields').then(r => (
//with 
axios.get('/getFields').then(r => {
...
}

couse it show me an error when vue compile if i use this clouser ()

@Leon012 Well, I did say that this was untested code. Perhaps instead of doing a map you can do something like this:

r.data.forEach(field => {
	this.fields.push({
		name: field,
		visible: true
	});
});

You're going to have to work through some of these finer points. I don't have your full code, so consider what I'm providing as pseudo code that you will have to modify to meet your exact requirements.

@lbecket i tryed to replace map with foreach but same error, and now i tryed to use this code but i still get error forEach is not a function. Maybe is the problem of data returned from laravel? on newtwork tab i see somethng like this for getfields

{1: "first_name", 2: "last_name", 3: "primary_email", 4: "primary_phone", 5: "lead_status", 6: "notes"}
1: "first_name"
2: "last_name"
3: "primary_email"
4: "primary_phone"
5: "lead_status"
6: "notes"

I don't have your full code...

also i am using your code example

And console.log(r.data.field) return undefined

@Leon012 The response from getFields() should be a one-dimensional array of strings. Try updating the getFields() method with the following and then you should be able to iterate over it.

public function getFields()
{
    $fieldsToQuery = collect(Lead::first())->keys()->toArray();
    $fieldsToIgnore = ['id', 'created_at', 'updated_at'];
   return collect(array_diff($fieldsToQuery, $fieldsToIgnore))->values()->toArray();
}

@lbecket This work. and the funksion pass, but now is showing another error for getleads

{message: "Array to string conversion", exception: "ErrorException",…}
exception: "ErrorException"
file: "C:\Users\ag\Desktop\vue+laravel\main_panel\vendor\laravel\framework\src\Illuminate\Database\Grammar.php"
line: 127
message: "Array to string conversion"
trace: [{function: "handleError", class: "Illuminate\Foundation\Bootstrap\HandleExceptions", type: "->"},…]

@Leon012 You have to pass a string to selectRaw, not an array. Edit your function as follows:

public function getLeads(Request $request)
{
	$fields = implode(',', $request->fields);
    return Lead::selectRaw($fields)->get();
}

@lbecket i tryed, but the result is same.

Maybe is the problem of data passed from vue to controller? The dd($fields); does not work, it mean the function drops before and maybe on $request->fields . But i am not understanding how to fix this. I have trued to return first_name in place of $request->fields

return Lead::selectRaw('first_name')->get();

And this is returning me the table with first name only

@Leon012 Sometimes you just need to break it down and work through it. The code below works for me:

Vue:

<template>	
	<div>
		<button 
			class="btn btn-info dropdown dropdown-toggle" 
			type="button" 
			id="setVisibility" 
			data-mdb-toggle="dropdown" 
			aria-expanded="false" 
		>
   			TABLE FIELD MANAGEMENT 
		</button>

		<ul 
			class="dropdown-menu prevent-close py-1 px-2" 
			aria-labelledby="setVisibility"
		>
			<li>
				<div class="layout-header pt-2 text-center">
					<h6>Hide/Show</h6>
				</div>

				<div 
					v-for="(field, i) in fields" 
					:key="i" 
					class="form-check form-switch"
				>
					<input 
						v-model="field.visible"
						class="form-check-input" 
						type="checkbox" 
						id="isHidden"
						@change="getData()"
					>
					<label 
						class="form-check-label" 
						for="isHidden"
					>
						{{ field.name }}
					</label>
				</div>
			</li>
		</ul>

		<table>
			<tr>
				<th 
					v-for="(header, i) in headers" 
					:key="i" 
					scope="col"
				>
					{{ header }}
				</th>
			</tr>

			<tr
				v-for="(lead, i) in leads"
				:key="i"
			>
				<td
					v-for="(field, j) in lead"
					:key="j"
				>
					{{ field }}
				</td>
			</tr>
		</table>


	</div>
</template>

<script>
	export default{
		data() {
			return {
				leads: [],
				headers: [],
				fields: []
			}
		},

		methods: {
			getData() {

				this.headers = this.fields.map(item => {
					if (item.visible) {
						return item.name;
					}
				});

				axios.post('/getLeads', {fields: this.headers}).then(response => {
					this.leads = response.data;
				});
			},
		},

		mounted() {
			axios.get('/getFields').then(r => {
				this.fields = r.data.map(field => {
					return {
						name: field,
						visible: true
					}
				});

				this.getData();
			});
		}
	}
</script>

Controller:

public function getFields()
{
    $fieldsToQuery = collect(Lead::first())->keys()->toArray();
    $fieldsToIgnore = ['id', 'created_at', 'updated_at'];
    return collect(array_diff($fieldsToQuery, $fieldsToIgnore))->values()->toArray();
}

public function getLeads(Request $request)
{
    $scrubbedFields = collect($request->fields)->filter()->toArray();
    $fields = implode(',', $scrubbedFields);
    return Lead::selectRaw($fields)->get();
}

@lbecket [UPDATED] Okay now this works, thank you. I have fixed and other little problems

@Leon012 Great! Please mark this thread as resolved.

@lbecket Just another little help. I want to add a filter for each column then i tryed something like this

<!-- Collapsed content ./ -->
                    <thead class="collapse" id="collapseFilter">
                        <tr>
                            <th></th>
                            <th v-for="(lead, i) in leads" :key="i">
                                <div class="filter-table col-12 d-flex">
                                    <select  id="" class="form-select" v-model="filter" @change="filterValue">
                                        <option v-for="(field, j) in lead" :key="j" :value="field">{{field}}</option>
                                    </select>
                                </div>
                            </th>
                            
                        </tr>
                    </thead>
                    <!-- ./ Collapse contet -->

I set this content as another header who will open only when i click filter button, So how can i display only column record, couse with this code i can see each row

@Leon012 If you intend to filter the records in your dataset, then you will have to pass the necessary parameters to the query with some kind of instruction on how the query should apply them. It sounds like you probably want to create another data object in your vue component and pass it to the getData query. It could look something like this:

filters: [
	{field: 'field 1', filter: 'value to filter on'},
	{field: 'field 2', filter: 'value to filter on'},
]

Pass the filters object through the axios call and then on the back end, parse the object and build up your query accordingly.

@lbecket I dont get it , iam trying but not understand very good how should do this filter. Can you help me with an example code?

@Leon012 The process is simple:

• Manage your filters in the component
• Pass the filters object to the back end through your axios call
• Parse the filters object on the back end and apply the necessary arguments to your query

If you don't know how to implement any of these steps, then you need to take a step back and familiarize yourself with the more fundamental aspects of Vue/Laravel.

@lbecket I know how to implement this steps, what i am not understanding is what should i put on filter object

filters: [
	{field: 'field 1', filter: 'value to filter on'},
	{field: 'field 2', filter: 'value to filter on'},
]

and should i do same as field query? I mean a get axios on mounted component and need to add field && filter on filters array with same method ?

@Leon012 The axios call can pass multiple objects and would look something like this:

axios.post('/getLeads', {fields: this.headers, filters: this.filters}).then(response => {
	this.leads = response.data;
});

i am not understanding is what should i put on filter object

Presumably you need the field on which you intend to filter and the value that you wish to filter against. If you need more, then include more.

@lbecket

the value that you wish to filter

This is what i am not understanding how can i get the value of field i want to filter?

It is something like this?

 this.filters = this.fields.map(item => {
                if (item.visible) {
                    return {
                        field: item.name,
                        filter: this.leads
                    }
                }
            })

@Leon012 No, what you've shown here is effectively building an object based on visible columns, but it is my understanding that you are trying to filter your records (rows) based on certain criteria. The suggestion that I had made was to create a filters data object that you pass to the back end, which would contain the field that you intend to filter and the value you wish to filter against. Based on the select menu that you described, which I assume contains the values that you want to use as filters, making a selection would call a method to update the filters object.

This topic is secondary to the original question of showing columns dynamically. If you're still confused by what I'm describing, then I suggest marking this thread as resolved and opening a new one that is more specifically focused on managing the state of your data based on an input.

@lbecket Maybe you misunderstood me, I'm thinking of doing a seach for each column, but instead of using an input type="search" I want to use an option && select tag where the values of the column should appear, Example The First Name column has the values John, Tod, James. I want that when I open the dropdown and click on the name of John, all the records that have this name will be displayed. I'm calling this a filter, and I want to do it for all the columns that are visible. Is it something similar to what you are trying to tell me with this example?

@Leon012 That's exactly what I'm describing, which is a different topic from the matter of dynamically displaying fields in a table.

As I described, each field's select menu fires a method that accepts arguments for the field and the value. The method manipulates your filters object such that it would now look something like:

[
	{field: "first_name", value: "John"}
]

You then pass the filters object though your getData call, parse it on the back end, and apply the filters to your query.

Again, we're delving into different matters from those in your original question about column visibility. Please resolve this thread and open any new ones that are specific to questions you may have about the mechanics of managing an object's state or dynamically building queries. Otherwise, this post becomes too convoluted to help anyone else who might be trying to display columns dynamically.

@lbecket Thank you for all. I have marekd as completed